Backend calcuation of "Show as % of grand total"

[RMDNA]

Hi all,

When I take a column ("Sales Quantity"), and choose "Show as % of grand total," what backend calculation is it making to the column to give me the result? Whatever it does, it works perfectly every time, and bypasses the need for me to write my own formula.

I'd like to reference that result in other calculations (specifically, using it in a rankx()), but I've never been able to create my own %GT column that works quite as well. Is there a catch-all formula the "show as" applies that I can break into its own column?

[Anonymous]

@RMDNA,

Please create the following measure in your product sales table.

%GT = SUM('Customer Sales'[Sales Quantity])/CALCULATE(SUM('Customer Sales'[Sales Quantity]),ALL('Product Sales'[Product Name]))

Regards,
Lydia

[RMDNA]

@Anonymous,

This works as a measure, but my goal is to use this in a running total, so I believe it needs to be a calculated column.

However, whenever I create % of Total or Rank as a calculated column, the ALL() appears to stop working and they no longer adapt with my slicers.

I had no issues getting everything to work when I only needed the visual to respond to slicers from its home table, but I can't get it to work when I filter by fields from other tables.

I feel like I'm missing something really obvious. Maybe it's a fundamental misunderstanding of how columns and measures works.

[Anonymous]

@RMDNA,

It is not possible to create a calculated column that can change dynamically change based on slicer selection, please review details in this KB.

In your scenario, you would need to create a measure, if you have questions about creating this measure, please share sample data and post expected result.

Regards,
Lydia

[Ashish_Mathur]

Hi,

The measure should be something like this

=SUM(Data[Sales])/CALCULATE(SUM(Data[Sales]),ALL(Data))

To get more specific help, i will need to see your PBI file.  Tell me exactly what you want there.

[RMDNA]

@Ashish_Mathur,

I've used that code to create a % measure before, but it never responds to filters from various other tables - it always displays the % of grand total, rather than the % of (whatever the current filtered) total is. The "Show Value as" option does what I need perfectly, and responds to all slicers.

This is part of a much larger problem, but this thread might provide you with some more information, and has a sample PBIX. I'm still trying to solve it myself piece by piece.