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
0
votes
1 answer

Dealing with (multiple) many2many relationships in Excel Powerpivot

I have a question about how to deal with multiple many2many relationships in Powerpivot. Here is my scenario: I have a bunch of products, where each product is assigned to at least one but potentially more categories, so for example my first table…
bratwoorst711
  • 77
  • 1
  • 4
0
votes
1 answer

DAX Rank by Date

I am Counting on Distinct ID's in a column - this is leading to the sum of the subtotals not equalling the grand total as follows: What I want to do is rank the Payment Dates in cronological order and select ONLY the highest date to display. In the…
mal-wan
  • 4,391
  • 4
  • 26
  • 37
0
votes
1 answer

Power pivot - Calculate Subtotal from a measure and repeat it in a column

I have created a measure in Power Pivot and I am able to calculate subtotal from it and repeat the values in another column as shown below. The problem is that it doesnt take into account the external filters on the pivot table. Table1 Here are the…
Sharat
  • 1
  • 3
0
votes
3 answers

DAX: Measure % of itself

I am looking for help on creating a measure that is a % of itself or % of the column total. Currently I have to create a duplicate of the aggregate (customers) and then in format values --> Show as % of Column total. I would like to make the measure…
Brandon
  • 70
  • 9
0
votes
1 answer

MDX Case statement

I am trying to use a MDX calculated measure to do a case statement on a column. I am using this code: Case [hist].[title] when "Mr" then "Test1 "Else "Test2"end when I save it it sets everything to Test2 in the new column even though Mr is in the…
rory83
  • 47
  • 7
0
votes
1 answer

Compare values previous date and second previous date in PowerPivot

I'm new to PowerPivot and DAX. I've followed some on-line tutorials. Now I have a small problem that I can't solve. I have the following data: Date Instrument Value 2016-07-27 A 100 2016-07-27 B 98 2016-07-26 A …
g3blv
  • 3,877
  • 7
  • 38
  • 51
0
votes
2 answers

PowerPivot RANKX Function not working

I am trying to rank the values in a calculated field called PMPM in powerpivot. The formula I'm using is RANKX(ALLSELECTED(Cost),[PMPM],,TRUE(),Dense) but the results are wrong. Sample data here The first column PMPM contains the fields I want to…
Priyanka Zaveri
  • 5
  • 4
0
votes
3 answers

How to hide Source Data Tables in Power Pivot

I unioned two tables (AccelerateData and Adjustments) in PowerQuery and loaded them into the data model (table name in the data model is AccelerateData). Afterwards I created a pivot table from the data model to analyze it. I want to make the data…
Stephan
  • 650
  • 7
  • 16
0
votes
1 answer

DAX - Create Dynamic Index Column

I am trying to create a calculated column in SSAS tabular model with DAX. I want a dynamic index column on a table. Meaning that the index starts at 0 when the table is filtered. Imagine you have a table like: item index apple 0 banana …
Kyle Weller
  • 2,533
  • 9
  • 35
  • 45
0
votes
2 answers

Using PowerQuery to pull multiple worksheets from multiple workbooks within multiple subdirectories

Essentially, I'm stuck with a hierarchy of .xls workbooks that I would like to compile using PowerQuery into a PowerPivot data model. Master Directory - Retailer Folder #1 - Brand #1 Workbook - SKU Worksheet #1 - SKU…
Austin Wismer
  • 281
  • 1
  • 4
  • 16
0
votes
1 answer

Missing rows when merging

I am working with Excel 2010, Power Query, and PowerPivot. I have a query named Database that consists of 60+ merged tables containing a total of 2m+ rows. I also have a separate query that consists of two columns PrimaryKey3 and Members (a count of…
Brendan
  • 3
  • 1
0
votes
1 answer

How to get value from nested relations in Power Pivot?

I'm using Power Pivot add-in to create a data warehouse for generate dynamic tables and graphs (strictly data source is Excel), but I have a problem whit a calculate in the relations. My data model is the following: My Snowflake data warehouse…
user3579840
  • 1
  • 2
0
votes
1 answer

DAX Grand Total With Existing Running Total

I have a scenario where my data source already contains a running total by week field. This is helpful, however if I have this data in powerpivot/tabular, the sum of that field is not accurate. Is there a trick or technique that will allow me to…
user1134307
  • 218
  • 2
  • 5
  • 16
0
votes
2 answers

Creating a DAX pattern that counts days between a date field and a month value on a chart's x-axis

I am struggling with a DAX pattern to allow me to plot an average duration value on a chart. Here is the problem: My dataset has a field called dtOpened which is a date value describing when something started, and I want to be able to calculate the…
Geoff
  • 3
  • 3
0
votes
2 answers

Compare running totals of different years

I'm trying to graph running totals by year week and slicing it by year. My columns are: [date], [week#], [month#], [year], [usermame], [transaction] Data Sample I'm currently using this…
Jeho
  • 15
  • 5
1 2 3
…
99 100