Combining Multiple Measures into a table for Conditional Formatting purposes...

[Auski]

Hey all,

I'm not even sure if it is possible, but I am trying to replicate a table in excel that conditionally formats the whole table using a grading colour scale.
Fig1

The figures contained within this table are weighted, so some calculations are required first.

The final measures used to get the weighted values are:

Weighted BOP = DIVIDE('Key Measures'[Sub_cases BOP],'Key Measures'[Active BOP])*1000

Weighted LNI = DIVIDE('Key Measures'[Sub_cases LNI],'Key Measures'[Active LNI])*1000

Weighted LSI = DIVIDE('Key Measures'[Sub_cases LSI],'Key Measures'[Active LSI])*1000

Weighted UNI = DIVIDE('Key Measures'[Sub_cases UNI],'Key Measures'[Active UNI])*1000

Weighted USI = DIVIDE('Key Measures'[Sub_cases USI],'Key Measures'[Active USI])*1000

Weighted WKO = DIVIDE('Key Measures'[Sub_cases WKO],'Key Measures'[Active WKO])*1000

This gets me the table I need (Fig2), but of course the formating will only do one field or measure at a time. I require the whole table to be formated.

Fig2


Is there some magic measure that will combine all of the above measures in there respective columns so I can format correctly.
Thanks for your time in reading this. Happy to answer any questions.

Thanks!

[Anonymous]

Hi @Auski 

Did the solution danextian  offered help you solve the problem, if it helps, you can consier to accept it as a solution so that more user can refer to, or if you have other problems , you can offer some information so that can probide more suggedtion for you.

Best Regards!

Yolo Zhu

[danextian · Accepted solution]

Hi @Auski 

Measures need to be conditionally formatted one by one. However, you can use a disconnected table to kind of convert these measures into column - kind of!  You can do so by using Enter Data. Assuming the table below is the disconnected table named MyMeasures, you can write a switch measure to return the value for each row

Measure

BOP

LNI

LSI

Combined Measures =
SWITCH (
    SELECTEDVALUE ( MyMeasures[Measure] ),
    "BOP", [BOP Measure],
    "LNI", [LNI Measure],
    "LSI", [LSI Measure]
)

Add Measure column to matrix column tile and the above measure to the value tile. Take note that disconnected tables dont have a relationship to your fact or dimension tables.