Forum Discussion
Conditional formatting Data bars based on the other field
Hi Ania26
First, does your data include a row for Total Sales? If not, you'll need to create a disconnected table. It's also unclear what you want to happen with the remaining part of the bar. Should it be colored in a lighter shade? If yes, you’ll need two separate measures - one for the main color and one for the lighter fill. If not, a single measure will be enough.
Disconnected Table -
CategoryWithTotal = VAR _total = ROW ( "Category", "Total", "Sort", BLANK () ) VAR _category = SUMMARIZECOLUMNS ( 'Table'[Category], "Sort", 1 ) RETURN UNION ( _total, _category )Measure -
Sales with Total = IF ( SELECTEDVALUE ( CategoryWithTotal[Category] ) = "Total", SUM ( 'Table'[Sales] ), CALCULATE ( SUM ( 'Table'[Sales] ), TREATAS ( VALUES ( CategoryWithTotal[Category] ), 'Table'[Category] ) ) )Note: I'm confident my reponse is not an untested AI-generated one 🙂
Please see the attached pbix.
Hi Ania26 ,
You want data bars where everything shows as a percentage of your total sales. Easy fix:
In your conditional formatting for data bars:
- Don't use "Percentage of field maximum"
- Instead, set a fixed maximum value of 100 (your total sales)
- Set minimum to 0
This way:
- Total Sales = 100 → full bar (100%)
- Shoes = 50 → half bar (50% of 100)
- Clothes = 20 → small bar (20% of 100)
The trick: Use a fixed scale instead of letting Power BI auto-scale each bar to its own maximum. When you set max value to 100, everything becomes relative to that total.
Go to Format → Conditional formatting → Data bars → set maximum value to whatever your total sales number is.
That's it - all your bars will now show as portions of the total instead of each one being scaled individually.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
Hello, thank you for reply. That works for total = 100 but when I apply some filter, total is lets say 52 and then this is not working.
Ania26 ,
For the dynamic filtering problem: When your total changes from 100 to 52 due to filters, you need a dynamic maximum value rather than a fixed one.
Create this measure:
Dynamic Max = SUM('Table'[Sales])Then in your data bars conditional formatting, use this measure as your maximum value instead of the fixed 100.
Your disconnected table approach works too @danextian, though it's quite elaborate for what could be solved with dynamic maximum values in the formatting settings.
Alternative approach: You could also use a simpler measure that calculates the total in the current filter context and use that as your scaling reference.
Both solutions handle the filtering scenario, just different levels of complexity depending on your specific needs.
Note: Some problems have elegant solutions that don't require disconnected tables and complex DAX patterns.