Percentage of Row Total Calculation

Question

I'm having an issue with the "show as % of row total" option in my pivot table.  When i turn on this option, the value that i get is correct however, when i go multiply the %age by "2013 Budget", the calculation is wrong  ie Instead of multipying 100,000 * 10%, the calculation is multiplying 100,000 *10,000   .  2013 Budget is coming from a seperate table (Previous Yr measure comes from Sales table) that has a relationship.  I have topline budget figure for the entire that i want to "spread" using the values from % of row total (based on my sales by month from the prior year).

I have [Previous Yr] in my PT which is defined as CALCULATE( DimDate[Current Yr]( DATEADD(DimDate[DateKey], -1, YEAR)) ).  I left this a No Calculation.  I then created a [budget pct] measure that was defined as =[previous yr] and then "Showed as" % of row total, and then added it to my PT.  I've tried using calculate in different context with no luck

Answer 1

I got my measure to work:

[Previous YR]   /  CALCULATE( SUM( sales[amt]) , DATESBETWEEN(DimDate[dateKey], DATE(2012,01,01), DATE(2012,12,31) ) )

and i applied it to my "2013 budget" and everything seemed to look and work properly until i started to add more levels of granularity to the row labels...

My "Accounts" and "Brands" look fine however, when i add "Sales Reps" and "Stores" is when i start to #'s that i dont want.  Not sure what i should do from here....Should there be a calculation against the Parent Row for these????

My Pivot table is on the left and my "Budget" is on the right.  My "Budget" is an All year value..

Answer 2

I got my measure to work:

[Previous YR]   /  CALCULATE( SUM( sales[amt]) , DATESBETWEEN(DimDate[dateKey], DATE(2012,01,01), DATE(2012,12,31) ) )

and i applied it to my "2013 budget" and everything seemed to look and work properly until i started to add more levels of granularity to the row labels...

My "Accounts" and "Brands" look fine however, when i add "Sales Reps" and "Stores" is when i start to #'s that i dont want.  Not sure what i should do from here....Should there be a calculation against the Parent Row for these????

My Pivot table is on the left and my "Budget" is on the right.  My "Budget" is an All year value..

Hi John,

Thanks for sharing this solution to solve this issue. It is benefit for other community members who have similar issue with you.

Regards,

---

Elvis Long
TechNet Community Support