Forum Discussion

Auski's avatar
Auski
Advocate I
1 year ago
Solved

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

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!
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

2 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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