cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arafmustavi
Frequent Visitor

Data Visualization with Rule Based Color in Column Graph

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?

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion
3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1658905686686.png

 

Then create a measure for conditional formatting :

Measure = IF(MAX('Table'[Consumed])>MAX('Table'[Budget]),"Red","Green")

Apply it to the columns

vjianbolimsft_1-1658905686687.png

 

vjianbolimsft_2-1658905686688.png

 

Turn on the Data labels:

vjianbolimsft_3-1658905686688.png

 

Final output:

vjianbolimsft_4-1658905686690.png

 

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:

vjianbolimsft_5-1658905686691.png

Use these measure to create constant line:

Line A = MAXX(FILTER('Table',[Category]="A"),[Budget])

Line B = MAXX(FILTER('Table',[Category]="B"),[Budget])

vjianbolimsft_6-1658905686693.png

Final output:

vjianbolimsft_7-1658905686695.png

 

 

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.

SpartaBI
Community Champion
Community Champion

@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:

SpartaBI_0-1658651747372.png


Then set it to this:

SpartaBI_1-1658651775598.png

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors