Calculating Percent of Total in Power Pivot Model

excel excel-formula pivot-table powerpivot dax
SAM244776 picture SAM244776 · Aug 5, 2013 · Viewed 14.7k times · Source

I have created a power pivot table as shown in the picture. I want to calculate quarter over quarter sales change. For which I have to divide for example corporate family "Acer" 's sales in 2012Q4 by sum of all the corporate family. I am using calculated measure to do this, but I am not sure what formula I can use.

enter image description here

My need is to create two columns, one for 2012Q4 percent of total and one for 2013Q1 percent of total. Then I will create another measure to find the difference. So the formula for 2012Q4 should be like this 1624442 / (1624442+22449+1200+16123) . Any idea which function can help me do it?

Answer

mmarie picture mmarie · Aug 11, 2013

It sounds like you are measuring the change in the percent of total for each corporate family from quarter to quarter. You will need to create 3 calculated measures. I'm not sure what your model looks like so I can't give you the exact formula, but here is the idea.

CurrentQtr%ofTotal:= Divide(Sum('Sales'[Units]),Calculate(Sum('Sales'[Units]), All['Product'[Corporate Family])))

PrevQtr%ofTotal:= DIVIDE(CALCULATE(Sum('Sales'[Units]), DATEADD(DimDate[DateKey], -1, QUARTER)),
CALCULATE(Sum('Sales'[Units]), DATEADD(DimDate[DateKey], -1, QUARTER), All('Product'[Corporate Family]))))

Change%ofTotal:= DIVIDE(([CurrentQtr%ofTotal]-[PrevQtr%ofTotal]),[PrevQtr%ofTotal])

I used the divide function because it handles divide by zero errors. You use the ALL function to remove the filter on the Corporate Family column from the filter context. The Change%ofTotal is just to find the differenc. I'm calculating % change but you may just want to subtract.

Here's the link to a good blog post on time intelligence. And here's one on calculating percent of total.

Related questions

Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More
How to SUM parts of a column which have same text value in different column in the same row

I have a column with names and a column with numbers: FirstName Name Number John Smith 17 John Smith 26 Peter Smith 116 Peter Smith 25 Franck Black 17 Luke Peterson 17 Luke Peterson 37 Names with same FirstName and Name represent the same person. I need …

Read More