Forum Discussion
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?
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.
5 Replies
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.
Date Level Value Marker 12/1/2018 0:00 A 68 1 12/1/2018 0:00 B 57 12/1/2018 0:00 C 38 0 12/1/2018 0:00 D 65 12/1/2018 0:00 E 42 1/1/2019 0:00 A 15 1/1/2019 0:00 B 13 1/1/2019 0:00 C 10 1/1/2019 0:00 D 16 1 1/1/2019 0:00 E 9 0 2/1/2019 0:00 A 13 2/1/2019 0:00 B 17 1 2/1/2019 0:00 C 9 0 2/1/2019 0:00 D 12 2/1/2019 0:00 E 13 3/1/2019 0:00 A 20 1 3/1/2019 0:00 B 15 3/1/2019 0:00 C 8 0 3/1/2019 0:00 D 19 3/1/2019 0:00 E 10 4/1/2019 0:00 A 18 1 4/1/2019 0:00 B 12 4/1/2019 0:00 C 11 4/1/2019 0:00 D 18 1 4/1/2019 0:00 E 10 0 Anonymous ,
Could you please share the sample data(table format) and give the logic of the conditional formatting?
Regards,
Jimmy Tao
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
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.
