Forum Discussion

moses07_ferns's avatar
moses07_ferns
Regular Visitor
1 year ago
Solved

Conditional Formatting Matrix Table By Rows

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 

"DAX "
@Conditional Formatting
Conditional Formatting within Matrix for Rows and Columns Text Fields
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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.

     

     

     

     

     

  • sanalytics's avatar
    sanalytics
    1 year ago

    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

     

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.

     

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    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's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.