Search code examples
powerbidax

Unable to put conditional formatting on my bars based on average values

So basically I am trying to put 2 different colours to bars that are above or below the average value. But the colour comes out same every time.

Screenshot 1

These are the DAX measures I am using.

avg sales bar colour = if([Total Sales]>[AVG SALES],"Above Average","Below Average")
AVG SALES = AVERAGEX(ALLSELECTED(Cafe_sales[transaction_date]),[Total Sales])
Total Sales = SUM(Cafe_sales[Total_Bill])

I have added a constant line with the "AVG SALES" Measure and using "avg sales bar colour" for conditional formatting the bar colours using rules if value is Above Average then one colour else different colour. Putting screenshots for reference.

Screenshot 2

I tried to create those DAX measures and made a dotted line for average in the bar plot. I wanted to put a darker colour on the bars who have above average values using conditional formatting but the colour is coming same for every bars.

Solution

  • Under Format your visual I set the Columns Color to a measure value.

    columns color function

    Color Categories Settings:

    settings

    Here is the updated measure I used:

    avg sales bar colour = if([Total Sales]>[AVG SALES],"Green","Red")

    I kept your Total Sales and AVG SALES measures the same.

    Result:

    result