Forum Discussion

HamidBee's avatar
HamidBee
Power Participant
4 years ago
Solved

How do I create a Colour Measure for each column?

I plan to to create multiple bar charts. I am going to create a custom formating for the data colours. I created a measure called 'colours' that makes the bar green if it is less than 5 and red if it...
Need Help
Show and Tell
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi HamidBee ,

    According to your latest file, here's my solution.

    1.Create the color measure.

     

    Colour = 
SWITCH (
    MAX ( '2021'[Letter] ),
    "A", IF ( MAX ( '2021'[Value] ) > 10, "Purple", "Green" ),
    "B", IF ( MAX ( '2021'[Value] ) > 10, "Blue", "Green" ),
    "C", IF ( MAX ( '2021'[Value] ) > 10, "Red", "Green" ),
    "D", IF ( MAX ( '2021'[Value] ) > 10, "Orange", "Green" ),
    "E", IF ( MAX ( '2021'[Value] ) > 10, "Purple", "Green" )
)

     

    2.For visual A, put Dates in Axis, Value in Values and put Letter in the vasual filter, then selete Letter is A. For visual B, select Letter is B, and so on.

     

     

    3.For each visual, use the color measure for data color, get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

HamidBee , This is what I would have done

1. Append both 2021, 2020 tables

2. Unpivot the columns A,B,C,D, E -https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://radacad.com/pivot-and-unpivot-with-power-bi

 

then joined with date table and create only one measure and one color measure

 

But, I not sure that is a fit for your solution

HamidBee's avatar
HamidBee
Power Participant
4 years ago

Thank you for your reply. I've unpivotted, deleted the old measures and appended the two tables into one. I've also removed any relationships and gotten rid of the calendar table. How do I create a measure for each letter in each year. If you take a look at my first power BI report I had a measure for the total of each letter for 2020 and then 2021 sepearetly. I'm not familar on how to type the formula when the tables are unpivotted like this. I'm familiar with how to do it when it's in columns but not when it's in rows. I get how the colour measure would work because I would reference the third column, "value". Although when typing the measure how do I type it if I'm refererring to a column name as I seem to get an error. This was how I had written it before:

 

Colour = SWITCH(TRUE(),[Total A (2020)]<5,"#006400", [Total A (2020)]>=5,"#800020")
 
Total A (2020) as you can imagine referred to a measure. If I change this to a column in a table it gives me an error. I hope I'm making sense as I can sometimes go into a tangent. I'm attaching the new file below:
 
 
Your help is appreciated.
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi HamidBee ,

    I download your sample, here's my solution.

    1.Create a calculated column.

    Year = YEAR('2021'[Dates])

    2.Create a color measure.

    Color =
VAR _SUM =
    SUMX (
        FILTER (
            ALL ( '2021' ),
            '2021'[Letter] = MAX ( '2021'[Letter] )
                && '2021'[Year] = MAX ( '2021'[Year] )
        ),
        '2021'[Value]
    )
RETURN
    IF ( _SUM < 5, "Green", "Red" )

    Then select the visual, turn on the Font color in the Conditional formatting.

    Apply like this

    Get the result.

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.