Conditional Formatting by Quarter

[Anonymous]

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]

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 · Accepted solution]

@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]

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

[v-yuta-msft]

@Anonymous ,

Could you please share the sample data(table format) and give the logic of the conditional formatting?

Regards,

Jimmy Tao

[Anonymous]

Hi Dipali - 

Can you pls share a sample file to get an idea of what you are trying to achieve?

I want to know if you are planning to use a matrix or a table visual, as we can use conditional formatting only in one of these at the moment.

Regards