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.

fhill's avatar
fhill
Resident Rockstar
6 years ago

If you are creating a Matrix, here's an option...

 

Create a Custom Column in your table containing your Matrix values.  (If you are performing sums or more complex calculations for each value, you may need to post sample data to help us determine the correct code...)

 

This will place (By SINGLE DATE Value - not a Range of Dates) a 1 or 0 in a Marker Column.  Note Matching Values (if you have 2 Max or Mins) will have duplicate colors)

Marker = IF(Table1[Value] = CALCULATE(MAX(Table1[Value]), FILTER( Table1, Table1[Date] = EARLIER(Table1[Date]))), 1,
IF(Table1[Value] = CALCULATE(MIN(Table1[Value]), FILTER(Table1, Table1[Date] = EARLIER(Table1[Date]))), 0, BLANK()))

 

Create your Matrix and Conditionally Format the Back ground Color based on the 'Marker' Column...

 

Background Color = Value  /  Based on 'Sum of Marker'

If value is 1 then Green  /  if Value is 0 then Red

 

This is my raw data, again if you have more complex data or calculations, we'll need to see the raw input to help further.

DateLevelValueMarker
12/1/2018 0:00A681
12/1/2018 0:00B57 
12/1/2018 0:00C380
12/1/2018 0:00D65 
12/1/2018 0:00E42 
1/1/2019 0:00A15 
1/1/2019 0:00B13 
1/1/2019 0:00C10 
1/1/2019 0:00D161
1/1/2019 0:00E90
2/1/2019 0:00A13 
2/1/2019 0:00B171
2/1/2019 0:00C90
2/1/2019 0:00D12 
2/1/2019 0:00E13 
3/1/2019 0:00A201
3/1/2019 0:00B15 
3/1/2019 0:00C80
3/1/2019 0:00D19 
3/1/2019 0:00E10 
4/1/2019 0:00A181
4/1/2019 0:00B12 
4/1/2019 0:00C11 
4/1/2019 0:00D181
4/1/2019 0:00E100