Calculating Percent of Total in Power Pivot Model

7
votes

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?

2
excelexcel-formulapivot-tablepowerpivotdax
This question should not have been closed as off topic. It is a valid question. The user is using a PowerPivot model and cannot simply switch his pivot table to show percent of total. This requires DAX calculations to be added to the model. We answer DAX questions all the time. – mmarie
Voting to re-open. PowerPivot models use DAX. DAX is a full on programming language. Don't let the Excel portion fool you. It's just a run-time host for the language. This isn't a suitable candidate for SU as they are focused on HW/SW/Networking. SO is for programming. Plus, we have a bloody tag for DAX. Don't close what you don't understand. – billinkc

2 Answers

9
votes

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.

1
votes

For percentages please follow the tutorial on the Tech on the Net.

Adding another column where you calculate a difference between two pivot columns will not work - this column is "unpivotable", as it relies on a column defintion. You would need to copy and paste pivot as values to another worksheet and do the extra calculation there.