Forum Discussion
Matrix visual conditional formatting issue
Delivery_KPI_Color =
VAR Key2 = UPPER(TRIM(MAX('Append KPI_(Actuals+Target)'[Key2])))
VAR ActualRaw = [Monthly Actual]
VAR TargetRaw = [Monthly Target]
VAR Actual =
IF(
ISNUMBER(ActualRaw),
ActualRaw,
VALUE(SUBSTITUTE(SUBSTITUTE(ActualRaw, "%", ""), "M", ""))
)
VAR Target =
IF(
ISNUMBER(TargetRaw),
TargetRaw,
VALUE(SUBSTITUTE(SUBSTITUTE(TargetRaw, "%", ""), "M", ""))
)
-- Treat blanks as 0
VAR ActualFixed = IF(ISBLANK(Actual), 0, Actual)
VAR TargetFixed = IF(ISBLANK(Target), 0, Target)
RETURN
SWITCH(
TRUE(),
Key2 = "PRODUCTION VOLUME - MONTHLY ACTUAL" && ActualFixed >= TargetFixed, 1,
Key2 = "PRODUCTION VOLUME - MONTHLY ACTUAL" && ActualFixed < TargetFixed, 0,
Key2 = "OEE - MONTHLY ACTUAL" && ActualFixed >= TargetFixed, 1,
Key2 = "OEE - MONTHLY ACTUAL" && ActualFixed < TargetFixed, 0,
Key2 = "PPA - MIX ADHERENCE - MONTHLY ACTUAL" && ActualFixed >= TargetFixed, 1,
Key2 = "PPA - MIX ADHERENCE - MONTHLY ACTUAL" && ActualFixed < TargetFixed, 0,
Key2 = "BRL (T1-T4) SCF" && ActualFixed <= TargetFixed, 1,
Key2 = "BRL (T1-T4) SCF" && ActualFixed > TargetFixed, 0,
Key2 = "DIOH (RM + WIP + FERT AT PLANT) - MONTHLY ACTUAL" && ActualFixed <= TargetFixed, 1,
Key2 = "DIOH (RM + WIP + FERT AT PLANT) - MONTHLY ACTUAL" && ActualFixed > TargetFixed, 0,
BLANK()
)----- I have it measure it fails to recongize the values greater then and less then target. How to fix it?@BhavinVyas3003@burakkaragoz
Hi Nisha357
Thank you for reaching out to the Microsoft Fabric Forum Community.
Please try below points,
- Switched from MAX() to SELECTEDVALUE() so the measure works correctly for each row in the matrix. MAX() can give unexpected results when multiple values exist in the context.
- Used [Monthly Actual] and [Monthly Target] directly because they are already numeric. No need to use VALUE() or SUBSTITUTE(), which can cause comparison issues.
- Handled blank values by treating them as 0 to prevent errors and ensure the conditional formatting logic runs smoothly.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
6 Replies
Hi Nisha357,
Can you please check if the data types are correct for [Monthly Actual] and [Monthly Target] are numeric measures or text?If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
[Monthly Actual] and [Monthly Target] are numeric measures
Hi Nisha357
Thank you for reaching out to the Microsoft Fabric Forum Community.
Please try below points,
- Switched from MAX() to SELECTEDVALUE() so the measure works correctly for each row in the matrix. MAX() can give unexpected results when multiple values exist in the context.
- Used [Monthly Actual] and [Monthly Target] directly because they are already numeric. No need to use VALUE() or SUBSTITUTE(), which can cause comparison issues.
- Handled blank values by treating them as 0 to prevent errors and ensure the conditional formatting logic runs smoothly.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Hi Nisha357
I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.
