Matrix Conditional formatting

[amol0512]

Hi, I wanted to color format Avg volume for each team in a row. this is data is shiftwise team wise Avg volume. I want to color red green based on Row Total of Avg Volume # in in each row. Green if it's higher than previous Avg Volume # and Red if it's less than previous Avg Volume #. Please help. Thanks in advance. 

[Anonymous · Accepted solution]

Hi @amol0512 ,

You could create a measure to get the previous [time range] and then get the coresponding [avg volumn].

Something like below:

measure =
var pre_time = calculate(max([[time range]]),filter(allselected('table'),[time range]<selectedvalue([time range])))

var pre_avg = maxx(filter(allselected('table'),[time range] = pre_time&&[team]=selectedvalue(team)),[avg volumn])

return
if([avg volumn]>pre_avg,"green","red")

At last, add this measure to conditional formatting value.

Best Regards,

Jay

[amitchandak]

@amol0512 , if previous is based on time bucket. Make sure the time bucket is in dimension and not in fact. and then create rank on asc order of time.

then you can create measures like

Time Rank = RANKX(all('Time'),'Time'[Start Time],,ASC,Dense) //YYYYWW format
measures
This = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Time'),'Date'[Time Rank]=max('Time'[Time Rank])))
Last = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Time'),'Date'[Time Rank]=max('Time'[Time Rank])-1))

Color measures =

Switch(True() ,

[This] -[Last] < 0, "Red", "Green" )

use this measure in conditional formatting using the field value option

How to do conditional formatting by measure and apply it to pie? : https://youtu.be/RqBb5eBf_I4

Rank-based TI , discussed here

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs