Evaluate which measure returns the result for the totals-measure

[Anonymous]

Hi there,

my aim is to conditionally format a scattar chart. 

As the picture bellow shows, the column 'Value' is the sum of each row. Each row has only one value, but separated in different measures. 

Value = 
[Done Risk green] + [Done Risk yellow] + [Done Risk red] + [Done Risk unplanned]+ [Sheduled Risk green] + [Sheduled Risk yellow] + [Sheduled Risk red] + [Sheduled Risk unplanned]

All [Measures] are structured the same but seacrh for different search variables

Sheduled Risk red = 
IF(MAX('NPLM Import'[Risk status (color)]) = "r", 
    IFERROR([Ø Annual Turnover (EUR) Sheduled], 0))

My Measure for the conditional formatting shall check in each case whether the returned 'Value' is e.g. in the column 'Done Risk red'. If so, it should return a HEX code

Basically, it is this idea:

Value Colour = 
SWITCH(TRUE(),
    [Value] = [Done Risk green], "#D7E4BD",
    [Value] = [Done Risk yellow], "#FFE8A1",
    [Value] = [Done Risk red], "#FFA1A1",
    [Value] = [Done Risk unplanned], "#D0D0D0",
    [Value] = [Sheduled Risk green], "#92D050",
    [Value] = [Sheduled Risk yellow], "#FFFF26",
    [Value] = [Sheduled Risk red], "#FF3300",
    [Value] = [Sheduled Risk unplanned], "#A6A6A6")

But that's not working, neither is Lookupvalue returning the anticipated result

Value Colour2 = 
SWITCH(TRUE(),
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk green]), "#D7E4BD", 
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk yellow]), "#FFE8A1",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk red]), "#FFA1A1",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk unplanned]), "#D0D0D0",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk green]), "#92D050",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk yellow]), "#FFFF26",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk red]), "#FF3300",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk unplanned]), "#A6A6A6")

Is there a better way to do so?

[Anonymous · Accepted solution]

Apparently, it does work with a minor addition:

Value Colour = 
SWITCH(TRUE(),
    ISBLANK([Value]), BLANK(),
    [Value] = [Done Risk green], "#acdc6f",
    [Value] = [Done Risk yellow], "#ffcc5d",
    [Value] = [Done Risk red], "#ff9594",
    [Value] = [Done Risk unplanned], "#D0D0D0",
    [Value] = [Sheduled Risk green], "#619822",
    [Value] = [Sheduled Risk yellow], "#ca8d00",
    [Value] = [Sheduled Risk red], "#ac1838",
    [Value] = [Sheduled Risk unplanned], "#A6A6A6")

[jthomson]

Are all these "done risk colour" things measures or columns? This would surely be much, much easier to calculate if there was just one column saying which column it is, then another column called value saying what the value is, which is an easy unpivot if it's columns

[Anonymous]

@jthomson 

I know columns would be far easier, but these are all measures. That's because it's necessary to have a slicer to select the date range for the measures.

Do you know a way with measures?