Conditional Formatting Matrix Table By Rows

[moses07_ferns]

I want to apply conditional formatting row by row based on the value of the measures. For eg. I have three measures 
Measure 1 :- Sales = SUM(Sales_Data[Total Revenue])

Prev Month Sales = CALCULATE([Sales],DATEADD('DATE'[Date],-1,MONTH))

Prev 2MONTHS SALE = CALCULATE([Sales],DATEADD('DATE'[Date],-2,MONTH))

The highest value in each row should be green and the lowest value should be red and the mid value should be yellow. Attached file for your reference.  https://drive.google.com/file/d/1YbPJ-K6aamwt1rW1FAqGpnGde6IFd3WB/view?usp=drive_link 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create three measure that rank three different measures in the table visualization. And then, in the conditional formatting option, I tried to configure like below for all three measures.
Please check the below picture and the attached pbix file.

[v-ssriganesh]

Hi @moses07_ferns,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @moses07_ferns,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[sanalytics · Accepted solution]

Hello @moses07_ferns 

You can follow below approach for your solution as well..
1) One disconnected Measure Table 
2) One Complete Measure.
Below is the code

ColorMatrix = 
VAR _HighestValue = 
MAXX(
 { [Sales],[Prev Month Sales],[Prev 2MONTHS SALE] },
 [Value]
)
VAR _lowestValue = 
MINX(
{[Sales],[Prev Month Sales],[Prev 2MONTHS SALE] },
[Value] )
VAR _MidValue = 
MAXX(
 FILTER(
    { [Sales],[Prev Month Sales],[Prev 2MONTHS SALE] },
 [Value] > _lowestValue && [Value] < _HighestValue
 ),[Value]
)
VAR _result = 
IF(
[MeasureConection] = _HighestValue,
"Green" , IF( [MeasureConection] = _MidValue, "Yellow",
IF( [MeasureConection] = _lowestValue,"Red"
) ) )
RETURN
_result

Below is the screenshot

pbix file is attached.

Hope it helps

Regards

sanalytics

[v-ssriganesh]

Hi @moses07_ferns,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Jihwan_Kim & @lbendlin for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create three measure that rank three different measures in the table visualization. And then, in the conditional formatting option, I tried to configure like below for all three measures.
Please check the below picture and the attached pbix file.

[lbendlin]

Since you chose to use three measures you will now need to create three more, one for each color calculation. It would have been much easier to use a disconnected calendar table.

You made the year and month slicer multi select. That will introduce additional ambiguity.  What should happen when the report user chooses 2020 and 2022, and Apr amd Jun ?

Maybe consider graphical solutions instead