Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Everyone,
I have a summarised Dataset where the Fields are like this.
Category -- Budget -- Consumed
A -- 1000 -- 500
B -- 4000 -- 4500
Now I want to Generate a Bar Graph with Budget vs Consumption. I want my Consumption bars to become red when it will cross the Budget Field and it will remain Green when it is below the Budget line.
Any DAX Formula or suggestion how to achieve this please?
Solved! Go to Solution.
@arafmustavi please find a link to a file with a solution:
Data Visualization with Rule Based Color in Column Graph 2022-07-24.pbix
Hi @arafmustavi ,
1. IF you want the whole consumed bar to become green or red:
As @SpartaBI mentioned before, you can use conditional formatting to change the color. However if you want to generate a Bar Graph with Budget vs Consumption, you will not be able to use conditional formatting. You can use line and clustered column chart to achieve this goal.
Please try:
First add values to the field like this:
Then create a measure for conditional formatting :
Measure = IF(MAX('Table'[Consumed])>MAX('Table'[Budget]),"Red","Green")
Apply it to the columns
Turn on the Data labels:
Final output:
2. If you want the part of the bar that below the budget line become green and the over part become red:
Please try:
Create these measure:
Below Budget = IF(MAX('Table'[Consumed])>MAX('Table'[Budget]),MAX('Table'[Budget]),MAX('Table'[Consumed]))
Over Budget = IF(MAX('Table'[Consumed])>MAX('Table'[Budget]),MAX('Table'[Consumed])-MAX('Table'[Budget]),0)
Then apply it to stacked bar chart:
Use these measure to create constant line:
Line A = MAXX(FILTER('Table',[Category]="A"),[Budget])
Line B = MAXX(FILTER('Table',[Category]="B"),[Budget])
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@arafmustavi please find a link to a file with a solution:
Data Visualization with Rule Based Color in Column Graph 2022-07-24.pbix
@arafmustavi I created this measure:
Color =
SWITCH(
TRUE(),
SUM('Table'[Consumed]) > SUM('Table'[Budget]), "Red",
"Green"
)
In the visual settings on the Bars colors I used the conditional formatting (Fx) option:
Then set it to this:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |