Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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
Not Working , It is filtered by top N and basic filtering
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |