The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
157 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |