Calculation of a percentage of the total value in the power model

Question

I created a power rotation table as shown. I want to calculate quarterly quarterly changes in sales. Why should I divide, for example, the corporate family "Acer" sales in 2012Q4 by the sum of the entire corporate family. I use a calculated measure for this, but I'm not sure which formula I can use.

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

Answer 1

It looks like you are measuring the change in percentage of the total for each corporate family from quarter to quarter. You will need to create 3 calculated measures. I don’t know 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 division by zero errors. You use the ALL function to remove the filter in the Corporate Family column from the filter context. Change% ofTotal - it's just finding the differences. I calculate the% change, but you can just subtract.

Here is a link to a good blog post on time. And here , when calculating the percentage of the total.

Answer 2

Percentage, please follow the Tech manual on the net .

Adding another column in which you calculate the difference between the two reference columns will not work - this column is "not available" because it relies on the definition of the column. You will need to copy and paste the pivot as values ​​into another worksheet and perform an additional calculation there.