• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot -show rolled-up % in pivot

Robbi

New Member
I am attempting to get the same percentage result as if I created a calculated field in a pivot table of numerator/denominator. However, I am trying to achieve this with PowerPivot as the source for the pivot table. I created new column 'Percent = [num]/[denom] (among other variations) but it does not calculate based on the summing of Num and Denom in the pivot table. Below "PERCENT" is what I am trying to get to. As mentioned, I could do this with the calculated field option in "Fields, items & sets" but that option is greyed out when connected to powerquery. Thanks for any help it is appreciated!!

Name Num Denom PERCENT The calc
Jill 13658 25603 53% 13658/25603 = 53%
John 2311 10285 22% 2311/10285 = 22%
Bob 356 2469 14% 356/2469 = 14%
 
Depends on your data structure. Without knowing underlying table structure can't give you good answer.

Having said that, here's general construct.
Num = SUM(Table[Column])
Denom = SUM(Table[Column])
Percent = DIVIDE([Num],[Denom],Blank())
 
Thank you Chihiro. I believe this is the answer but getting it into the pivot table correctly has been a challenge.
If Jill has 3 records where the percent columns are calculated at .50, .70 and .30, then the percent field is coming in as 1.5
What I am looking for is for Jill's "Percent" when pulled into the pivot table to be the sum of her numerator over the 3 rows divided by the sum of her denominator over her 3 rows.
Jill's total Numerator (1000)/Jills total denominator (4500) = 22%
I really appreciate any further help you have I have been at this for a while!
 
You don't add the calculation as columns. But as measures.

So it's evaluated according to context of visual or pivot table you use those measures in.
 
You don't add the calculation as columns. But as measures.

So it's evaluated according to context of visual or pivot table you use those measures in.
Chihiro! I can't thank you enough for the help. That's the piece I was missing.
 
Back
Top