Forum Discussion
Matrix - Conditional formatting based on column values in three rows.
I need to compare values in the column in three rows within a group and highlight the group is atleast one value is different. For example:
In the above example I want to highlight all rows under "A" as the third record has a different amount than the other two. The group for B will should not be highlighted as all three amounts are same. Can this be done in PowerBI using the Matrix viz.
Hi jjagadish ,
There might be a better way of doing this but you can use the following measure for conditional formatting:
Flag =var value1=CALCULATE(SUM(Data[Value]),Data[ID]=1,ALLEXCEPT(Data,Data[Code]))var value2=CALCULATE(SUM(Data[Value]),Data[ID]=2,ALLEXCEPT(Data,Data[Code]))var value3=CALCULATE(SUM(Data[Value]),Data[ID]=3,ALLEXCEPT(Data,Data[Code]))return if(value1=value2 && value2=value3 && value3=value1,1,0)Check the attached filePlease accept this as a solution if your question has been answered !!
Appreciate a Kudos 😀
2 Replies
Hi jjagadish ,
There might be a better way of doing this but you can use the following measure for conditional formatting:
Flag =var value1=CALCULATE(SUM(Data[Value]),Data[ID]=1,ALLEXCEPT(Data,Data[Code]))var value2=CALCULATE(SUM(Data[Value]),Data[ID]=2,ALLEXCEPT(Data,Data[Code]))var value3=CALCULATE(SUM(Data[Value]),Data[ID]=3,ALLEXCEPT(Data,Data[Code]))return if(value1=value2 && value2=value3 && value3=value1,1,0)Check the attached filePlease accept this as a solution if your question has been answered !!
Appreciate a Kudos 😀
jjagadish , You can use a color measure , but that will work only for values not for row
Switch(Max(Table[Code])
"A", "Green",
"B", "White",
"White"
)
Use this measure in conditional formatting using the field value option
How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4
