7

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?

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
SAM244776
  • 1,375
  • 6
  • 18
  • 26
  • 11
    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 Aug 11 '13 at 04:07
  • 7
    Voting to re-open. PowerPivot models use DAX. [DAX](http://technet.microsoft.com/en-us/library/gg413422.aspx) 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 Aug 11 '13 at 05:10

2 Answers2

9

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.

mmarie
  • 5,598
  • 1
  • 18
  • 33
1

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.

Juliusz
  • 2,096
  • 2
  • 27
  • 32
  • I am using powerpivot instead of simple pivot. I guess I can write measures in power pivot which is not possible in pivot. The problem with pivot is if I change the filters then my % changes...which I can overcome in power pivot..only problem is I am not able to get the sum properly... – SAM244776 Aug 05 '13 at 13:50
  • 2
    It sounds like you want to compute percent of total for the current quarter and the previous quarter. Two blog posts will help: http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx. Look at the [StoreSalesPrevYr] calculation. You can replace year with quarter. To get the percent of total see http://www.madronasg.com/blog/favorite-quick-easy-dax-calculate-percent-total-scenario#.UgcNUG33Ngg. Basically use something like =Calculate(Sum('Sales'[Units]), ALL('Product'[Corporate Family])). You will have a Current Qtr % calc, and a Prev Qtr % calc. – mmarie Aug 11 '13 at 04:21