Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional Formatting by Quarter

Hi All,   I am trying to apply conditional formatting on minimum & maximum values per quarter as below. Can anyone guide me on the same?    
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    So the percentage value in your matrix is a measure you have created and you need to color the highest and lowest measure value per quarter, right?

     

    If so, you need to convert the measure to calculate column and then create a measure to set the color rule:

    Color =
VAR Max_Percentage =
    CALCULATE ( MAX ( Table[Percentage] ), ALLEXCEPT ( Table, Table[quarter] ) )
VAR Min_Percentage =
    CALCULATE ( MIN ( Table[Percentage] ), ALLEXCEPT ( Table, Table[quarter] ) )
RETURN
    SWITCH ( Table[Percentage], Max_Percentage, "Red", Min_Percentage, "Blue" )

    Then click condition formatting pane of the matrix visual and select Field Value based the Color measure:

     

     

    Community Support Team _ Jimmy Tao

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi All,

 

Thanks for response.

 

I am using a matrix here, The numbers in output are a calculated measure.

I don't see an option of attaching pbi file here.

 

I am using a calculated measure to derive the % difference between quarter vs previous quarter.

Below are the screen shot of raw data & output table for which I need to highlight (conditional formatting) for highest & lowest value per quarter.

 

 

Regards,

 

Dipali

                        

v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

Anonymous ,

 

So the percentage value in your matrix is a measure you have created and you need to color the highest and lowest measure value per quarter, right?

 

If so, you need to convert the measure to calculate column and then create a measure to set the color rule:

Color =
VAR Max_Percentage =
    CALCULATE ( MAX ( Table[Percentage] ), ALLEXCEPT ( Table, Table[quarter] ) )
VAR Min_Percentage =
    CALCULATE ( MIN ( Table[Percentage] ), ALLEXCEPT ( Table, Table[quarter] ) )
RETURN
    SWITCH ( Table[Percentage], Max_Percentage, "Red", Min_Percentage, "Blue" )

Then click condition formatting pane of the matrix visual and select Field Value based the Color measure:

 

 

Community Support Team _ Jimmy Tao

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