Bar color to change depending on number

[stvesanity]

Hello Experts,

Seeking for your help, please.

Chart type: Bar chart

Assuming that the normal value is 100, how will:

if lower than 100, will appear in the bar and will have color

if higher than 100, will appear in the bar and will have color

something like this..

[MFelix]

Hi @stvesanity ,

You need to create 3 measure to make this type of visualization:

Value below 100 = IF((Sum('Table'[Value]) <= 100  ), 100 - SUM('Table'[Value]))

Value 100 = IF (Sum('Table'[Value]) > 100, 100 , SUM('Table'[Value]))

Value above 100 = IF(Sum('Table'[Value]) >= 100 , Sum('Table'[Value]) - 100 )

Now setup your 3 measures on a stacked colum  chart by the order below:

Value 100

Value below 100

Value above 100

Select the needed colour

If you need you can change the 100 to a selected value from a table or another type of calculation.

PBIX file attach.

[stvesanity]

Appreciate your swift response, @MFelix 

Just curious

what if the default value varies?

to give more context, this is for total work hours in week and monthly

so I'm planning to show 

if in my chiclet, i selected week of xx, then the expected hours on that week will show in my bar or if i selected week of xx & xx , then the total expected hours will show for those 2 wks or if I show the montly view, then it will show the expected hrs on that month.

[MFelix · Accepted solution]

Hi @stvesanity ,

In that case you need to replace the 100 on the 3 formulas by the calculation you need.

Assuming you have a measure that calculates that value you just need to redo your measures to:

Value below Default= IF((Sum('Table'[Value]) <= [Default Measure]  ), [Default Measure] - SUM('Table'[Value]))

Value Default = IF (Sum('Table'[Value]) > [Default Measure], [Default Measure] , SUM('Table'[Value]))

Value above Default = IF(Sum('Table'[Value]) >= [Default Measure] , Sum('Table'[Value]) - [Default Measure] )