Percentage against sub totals

Anything related to PowerPivot and DAX Formuale
jefferson airplane
Posts: 6
Joined: Wed Jul 25, 2018 7:05 pm

Percentage against sub totals

Postby jefferson airplane » Wed Aug 01, 2018 8:40 pm

Hi guys

I have this "likely" silly question..perhaps you can help me...

Example below:

% column is obviously the % against of values against total - calculated with the measure on the right.

I need to have the % against sub-totals (in red) kept when all filters (on Project#) are off.

As you can see, when filters are off, excel calculate the % against the grand total... I am struggling to identify the right formula to apply...

Is there any way to do this?

Thanks in advance
Untitled.jpg (206.15 KiB) Viewed 695 times

Posts: 1019
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Percentage against sub totals

Postby MattAllington » Thu Aug 02, 2018 7:42 am

There is a way, but it is quite involved. I teach this in my live training classes. The trick to this is to break the problem into pieces and solve one piece at a time. You need to write a formula that returns the sub total into the pivot table. Something like

SubTotal = CALCULATE([Total Value],ALL(table[Centre]))

Once you have the subtotal, you can divide the result by the subtotal.

Does that make sense?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests