Conditional formatting query

[PeteSG]

Hello,

I am trying to solve an issue I have in which I have a matrix with list of departments down the left hand side and training metrics across the top. For each metric I want to apply formatting thats green if >=90% and red if <90% which is fine. However, there is one metric (column) that needs to be formatted at green if >=95% and red if <95% is this possible?

Any help would be appreciated,

Regards,

Pete.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Jai-Rathinavel  offered, and i want to offer some more information for user to refer to.

hello @PeteSG ， you can refer to the following sample.

Sample data 

In this sample if metric=test4, then if the value >=0.95 then green else red, for other metric, if the value >=0.9 then green else red.

Create the a measure.

MEASURE =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table'[metric] ) <> "test4", IF ( SUM ( 'Table'[Value] ) < 0.9, "red", "green" ),
    SELECTEDVALUE ( 'Table'[metric] ) = "test4", IF ( SUM ( 'Table'[Value] ) < 0.95, "red", "green" )
)

Then create a matrix visual, and put the following field to the visual.

Then click the condition formatting of the value, and put the measure to it.

Output

Best Regards!

Yolo Zhu

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

[Jai-Rathinavel]

Hi @PeteSG , This can be achieved using Cell elements property present in the matrix.

I believe this is the expected result from your end, where I am tracking two different metrics but formatted based on different conditions.

Attaching PBIX for reference: Download PBIX

If I had answered your question, please mark this post as a solution

Thanks,

Jai

[PeteSG]

Hi Jai,

Unfortunately there seems to be a bit more to it than this, I am calculating the compliance % in a measure. So I am unable to select the columns separately. 

Is it possible that I can create a filter within a measure? So if I divide the sum of one column by another column then filter. If so how would I write this?

Kind Regards,

Pete.

[Jai-Rathinavel]

@PeteSG I have used sales and volume as an aggregated field sum(sales) and sum(volume). You can just create your measures and pull it in your values section and leave columns section empty. 

[PeteSG]

Hi Jai-Rathinavel,

If I take out the metric columns I just get the grand total when I need the metrics calculated. I cant pull each metric % through to values individually as there's hundreds of metrics to include so I used an overall calculated measure.

Would you know how to produce a value just for a single metric? I.e. Divide sum of compliant by sum of required but only for "xxx" metric? That could work but I'm not sure how to specify the metric in the dax code.

Thanks,

Pete.