Forum Discussion

jlkrawcyk's avatar
jlkrawcyk
Regular Visitor
4 years ago
Solved

Creating a trend based on metrix in a matrix

Hi,

 

I have the following matrix with many metrics, and have been asked to create a "trend" column based on the metrix "OTC" :

 

 

They would like to have a column that shows something like "needs review" or "ok"  based on:

Needs Review = If the average of reporting months 2/1/2022, 3/1/2022, 4/1/2022 is less than current reporting month (5/1/2022)  and/or < 60% otherwise "ok"

 

Example: above visual (23+50+71)/3=48% is less than 76% and greater than 60% so we would show as "Ok"

 

I hope I explained clearly 🙂

 

Thank you in advance,

Jordan

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jlkrawcyk ,

     

    If you want this:

    Please try:

    Column = 
var _pre3Months=PARALLELPERIOD ('Table'[Date], -3, MONTH )
var _avg= IF([Sub]="OTC",CALCULATE(AVERAGE('Table'[Value]),FILTER('Table', [Date]>=_pre3Months && [Date]<EARLIER('Table'[Date]) && [Sub]="OTC" )),BLANK())
return  IF( _avg<>BLANK(), IF( _avg<0.76 && [Value]>0.6, "OK","Needs Review") )

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jlkrawcyk ,

     

    If you want this:

    Please try:

    Column = 
var _pre3Months=PARALLELPERIOD ('Table'[Date], -3, MONTH )
var _avg= IF([Sub]="OTC",CALCULATE(AVERAGE('Table'[Value]),FILTER('Table', [Date]>=_pre3Months && [Date]<EARLIER('Table'[Date]) && [Sub]="OTC" )),BLANK())
return  IF( _avg<>BLANK(), IF( _avg<0.76 && [Value]>0.6, "OK","Needs Review") )

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.