Stack Overflow
Stack Overflow

Questions tagged [powerpivot]

An in-memory OLAP cube that can for instance be installed and used in Excel. It allows for fast analysis of huge datasets.

PowerPivot is part of the Microsoft Business Intelligence suite and uses SQL Server in the background, but it is geared towards the data-savvy business oriented person. It allows you to easily load data from different sources, put them in a relational database-like format and create relationships between the tables.

When all data is loaded, an OLAP store is created in the background and you can do real time analysis by selecting and filtering to your heart's content.

Also included is a powerful query language called DAX (Data Analysis eXpressions) which use an Excel style syntax to allow the user to perform complex calculations.

Power-Pivot datasets can also be served as part of SharePoint and Microsoft has even put at least one all-in hardware appliance on the market in collaboration with hardware vendors.

The following links provide useful reference information:

Further interesting PowerPivot articles can be found here:

1828 questions
2
votes
1 answer

DAX - Sum values in many-to-many relationships

I'm working in Excel, PowerPivot. I have 2 tables. Table 1 has locations of inventory types. Notice that there can be several items of a type in a room: Kategory Room Serial number Pump Room 1 SN1 Pump Room 1 SN2 Pump Room…
T C
  • 304
  • 1
  • 2
  • 12
2
votes
1 answer

I am looking to filter data based on multiple conditions across different column

Let's say I have this fictional table. This table shows me a fictional list of customers and the various claim type for the services they have decided to have, which outlet they went to, if they are a mammal or bird. Outlet Name Claim…
ProperDataSet
  • 31
  • 7
2
votes
3 answers

Accumulate values over years in DAX

My table has a column that calculates the cumulative total year-to-date (YTD). The column is Balance BRL. It works ok using the DAX function DATESYTD. The column Balance BRL is perfectly achieved using the DAX below: Balance BRL = CALCULATE ( …
Arnold Souza
  • 601
  • 5
  • 16
2
votes
0 answers

Calculating Storage Occupancy from History of Multiple Item Stages - Measure or Calculated Column?

I have this dataset (see sample here) of items with time stamped stages. Some stages mean the item is occupying space in the storage. The other stages mean it's not. I need to use Power Pivot 2016 (not Power BI) to produce the chart below for avg.…
IABKO
  • 21
  • 3
2
votes
1 answer

Power pivot relation between tables from data model created with power Query

I made a relation 1 to many between to table but when I try to use it in a pivot table it fails. I get the usual yellow message saying it may lack a relation. When I let it try to detect one it fails to find anything possible and when I check the…
Alexandre Rivara
  • 113
  • 1
  • 11
2
votes
0 answers

Create application/atom+xml result

I have an API on GoogleCloud and need to define an endpoint which returns a feed usable in Excel PowerPivot and Power BI. I have found this library using feed.ToAtom(), but the created feed does not work in PoverPivot, I get the following error: The…
Emaborsa
  • 2,360
  • 4
  • 28
  • 50
2
votes
2 answers

Powerpivot sql query fails - Unable to convert a value to the data type requested for table column

I'm trying to execute a sql query through powerpivot and I get the following error "Unable to convert a value to the data type requested for table (massive hex string) column (name)" SELECT [name], [table].[group],[table2].[group2] ,SUM([number])…
user692898
  • 109
  • 2
  • 8
2
votes
2 answers

Excel data model: Return last non blank

I am struggling with getting only the last non blank value for each ticket. In the Power Pivot measure, I have used a function LASTNONBLANK() , but the outcome was not reliable - probably because data model does not consider the original order of…
MalyMajo
  • 75
  • 3
2
votes
1 answer

PowerPivot - How to Filter Dimension to Get Value

I am working on a PowerPivot report that has the following…
Shawn
  • 1,871
  • 2
  • 21
  • 36
2
votes
1 answer

DAX formula to find second minimum with extra criteria

I have a problem that involves two tables, one with some costs (table stops at value, I have put in Expected Value what I want to see in my explicit measure), one with cities sorted by clusters. I need to create an explicit measure that returns the…
Lezira
  • 35
  • 4
2
votes
1 answer

Finding Percentile of a calculated measure in PowerPivot / DAX

A table that is similar to the data set I am working on (although much simpler) is below that I would like to calculate some measures on and then find the percentiles of the measures. Table Name: Data Owner AgeRating OtherRating A 1 …
whh4000
  • 905
  • 1
  • 12
  • 30
2
votes
1 answer

Delete Power Query steps after loading to Power Pivot data model

I need to delete query steps after loading the data into model. The reason is to hide the sources, protect our know-how, or maybe I'm just not very proud of what I've done ;). But when I delete PQ connections or change "Load To" option, also the…
AdamL
  • 12,421
  • 5
  • 50
  • 74
2
votes
1 answer

How can I stop pivot table filtering from breaking my 14d rolling average measure?

I have two tables: FactTable and Calendar. My Fact table has consumption data for a variety of objects, and I want to create a 14 day rolling average measure. I have been somewhat successful, but I've noticed that whenever the consumption quantity…
LeroyBrown
  • 55
  • 6
2
votes
1 answer

Combine SELECTCOLUMNS and ORDER BY in DAX

This works, it selects multiple columns: evaluate SELECTCOLUMNS(branches, "The branch code", branches[code], "The branch name", branches[name], "The branch city", branches[city]) This also works: evaluate branches …
ps0604
  • 1,227
  • 23
  • 133
  • 330
2
votes
1 answer

Why is using Power Pivot getting the Data Model deleted in Excel?

I am using Power Pivot to do some analysis and then make Pivot tables from the data model. There are more then 1 interconnected tables. The tables have been created using Power Query. The relationships are created using Power Pivot. After my work is…
Zahid
  • 21
  • 2
1 2 3
…
99 100