## 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

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?

Attachments
Untitled.jpg (206.15 KiB) Viewed 306 times

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

### Re: Percentage against sub totals

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".
[URL=https://exceleratorbi.com.au/power-bi-online-training/]Power BI Online Training[/URL]