% of grand total not to change when filter applied

[bo_afk]

Hi,

I currently have a visual displaying % of sales of items by item category. i.e. how much does each item category contribute to the total number of sales. 

However, when I filter for a particular category to see it in more detail (since there are so many), the visual displays a percentage of 100% because this is technically correct since the grand total of the current selection only includes that one category.

How can I get the visual to display the % of total sales, regardless of any filters applied?

Thanks

afk

[Greg_Deckler]

You will have to create a measure in which you use ALL or ALLEXCEPT. Can't be more specific without data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous · Accepted solution]

@bo_afk - You can create a measure that is % of grand total with the following pattern:

Category Percent of Grand Total =
DIVIDE ( [YourMeasure], CALCULATE ( [YourMeasure], ALL ( YourTable[Category] ) ) )

Cheers!

Nathan

[Anonymous]

Not Working , It is filtered by top N and basic filtering 

[bo_afk]

Hi @Anonymous 

This has done the job, thank you!

One thing though, I would now like to filter out one of these item categories from the whole report. But when I do, the grand total % (which should be 100) is reduced by the value of this category.

Is it possible for the measure to recalculate the grand total with this category taken out so that the grand total % remains as 100%? I know this is slightly contradictory to the formula you provided.

Hope this makes sense.

afk

[Anonymous]

@bo_afk  - You wouldn't be able to do that with a filter/slicer on the category column, but you filter/slice on an identical column in the same or another table.