cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bo_afk Member
Member

% of grand total not to change when filter applied

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. 

 

Percentage.JPG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: % of grand total not to change when filter applied

@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

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: % of grand total not to change when filter applied

@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

View solution in original post

Super User
Super User

Re: % of grand total not to change when filter applied

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

bo_afk Member
Member

Re: % of grand total not to change when filter applied

Hi @natelpeterson 

 

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

Super User
Super User

Re: % of grand total not to change when filter applied

@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.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)