Forum Discussion

amol0512's avatar
amol0512
Icon for Helper I rankHelper I
4 years ago
Solved

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 i...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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