Forum Discussion
Matrix Conditional formatting
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.
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
2 Replies
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/aU2aKbnHuWsHi 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
