Forum Discussion

Clockwise's avatar
Clockwise
Helper I
7 months ago
Solved

Conditional Formatting a Bar Chart using a measure

I am trying to simply add this conditional color to bar chart such that if it is more than average one color be used and if it is less than average another color. Take Red and Green.    Here is the...
Need Help
  • Clockwise's avatar
    Clockwise
    7 months ago

    The soluton did not work exactly but were useful. I was able to resolve it in a differnet way. This can be closed.

cengizhanarslan's avatar
cengizhanarslan
Super User
7 months ago

1) Total Past Due (measure)

Total Past Due :=
SUM ( 'Summary'[Total Past Due] )

 

2) Average Total Past Due (measure)

If you mean “average across categories currently shown”, do:

Avg Past Due (by category) =
AVERAGEX (
    ALLSELECTED ( 'Summary'[Category] ),   -- replace with your axis field
    [Total Past Due]
)

 

3) Color measure (return hex is safest)

Bar Color PastDue =
IF ( [Total Past Due] > [Avg Past Due (by category)], "#00B050", "#C00000" )

 

4) Apply it to the chart

Format pane → Data colors → fx

  • Format by: Field value

  • Based on field: Bar Color PastDue

  • Summarization: First

Now each bar should color based on whether it’s above/below the average.

Clockwise's avatar
Clockwise
Helper I
7 months ago

Thanks cengiz for replying again. Correct me if I am wrong but sounds apart from the 1st and 2nd definition I have tried this. Yes, there is no category and I am doing for all the data.

Also if I do not add 

SELECTEDVALUE in front of columns name in the measure definition, I get the error of 
" A single value for **** can not be determined" which I am sure you are familiar with.  Maybe in your case you won't need it. 
 
Any thoght why it wokrs for Effects background but not for Bars? 
  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago

    That “single value cannot be determined” error is exactly why you should aggregate the column into measures.

    Total Past Due =
SUM ( 'Summary'[Total Past Due] )

     

    If Average_TotalPastDue is already a column with the same value repeated, still create a measure:

    Average Past Due (overall) =
AVERAGE ( 'Summary'[Total Past Due] )