How do I create a Colour Measure for each column?

[HamidBee]

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 is greater than or equal to 5. However, I have realised than I will have to create a multiple measurs for each column (A,B,C,etc.). This will be very lengthy and perhaps uncessary. Is there a way that I could only have one measure that would work for all the charts I create?. Even if....wait for it....I introduce a slicer.

I'm sharing the file here so if anyone would like to play around with it.

https://www.mediafire.com/file/omamwc06v7zw93e/Power_BI_Example.pbix/file

Any help would bre greatly appreciated.

[HamidBee]

Thank you for your reply. Although what I was looking for is a bar chart. Something like this:

So if it is greater than 0 then it would be green and if it is less than 0 then it would bre red. I wanted to create seperate bar charts for each letter. I am trying to do it by creating one calculated measure for all of the bar charts instead of seperate ones. Please find a link to the full question here along with the latest pbix file:

https://community.powerbi.com/t5/Desktop/Colour-DAX-Measures-aren-t-working-as-they-should/m-p/22534...

It seems that what I am trying to do is not possible but if you have figured out a way please let me know. 

Thank you

[v-yanjiang-msft · Accepted solution]

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.

[HamidBee]

How would I ammend the code if I had measures instead? so for example I have 5 measures which calculate the total of A,B,C,D and E. e.g:

Total A = calculate(sum('2021'[Value]), Filter('2021', '2021'[Letter] ="A"))

How would one ammend your code to include all possibilities for each of the 5 measures. 

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" )
)

[v-yanjiang-msft]

Hi @HamidBee ,

By my test, if you would like to write seperate measures for letter, you have to write seperate color measures as show in your above thread link. Otherwise, you can use my solution and achieve the same result.

Best Regards,
Community Support Team _ kalyj

[amitchandak]

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

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:

https://www.mediafire.com/file/omamwc06v7zw93e/Power_BI_Example.pbix/file

Your help is appreciated.

[v-yanjiang-msft]

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.