Forum Discussion
Conditional formatting Data bars based on the other field
Hello, I have a Total Sales= 100 and in those 100, 50 is Shoes, 20 is Clothes and 30 is Other. How can I color bars using conditional formatting, that it shows Total Sales=100% fully colored , then second bar Shoes is colored half , trird bar 20%. What I want to do is to color using conditinal 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.
6 Replies
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.
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
Option 1: Conditional Formatting with Data Bars (in a Table)If you only want to show the percentage visually in a table with data bars, you can use this measure
Sales % Measure = IF( HASONEVALUE(Sheet1[Category]), MAX(Sheet1[Sales]) / CALCULATE(SUM(Sheet1[Sales]), ALL(Sheet1)), 1)- Add a Table visual
- Put Category and Sales % Measure in it
- Apply Conditional Formatting >> Data Bars to Sales % Measure
Option 2: Clustered Bar Chart with Total Row and Individual Colors
If you want a Clustered Bar Chart that includes a Total bar and different colors per bar (like Image 3 & 4), follow these steps:
Step 1: Create a calculated table with a Total row:
SalesWithTotal = UNION ( SELECTCOLUMNS( Sheet1, "Category", Sheet1[Category], "Sales", Sheet1[Sales] ), ROW("Category", "Total", "Sales", SUM(Sheet1[Sales])))Step 2: Create a percentage measure (excluding "Total" from denominator):
Sales % = DIVIDE( [Sales], CALCULATE( SUM(SalesWithTotal[Sales]), FILTER( ALL(SalesWithTotal), SalesWithTotal[Category] <> "Total" ) ) )Step 3: Create a Clustered Bar Chart:
- Axis >> Category from SalesWithTotal
- Values >> Sales % measure
Step 4: Assign colors:
- Go to Format visual >> Bars
- Under “Apply settings to”, assign separate colors for each category
Hi Ania26
Thank you for posting your question on the Microsoft Fabric Community Forum, and thanks as well burakkaragoz , danextian and rohit1991 for the continuous support and assistance.
Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.
Thank you.
