Filter for value and divide by average of a range

[RDzeketey]

I am trying to solve a problem. I have two tables and I need to filter for the current period value in Table 2 and divide it by the average of the filter of the first six months in Table 1.

Please see below for illutstration

[harshnathani]

 Hi @RDzeketey ,

If your YYYYMM is a Date Column.

You can try this.

Lookup Table2 = LOOKUPVALUE(Table2[Value],Table2[YYYYMM],MAx(Table1[YYYYMM]))

Average 6 months =

 

DIVIDE(CALCULATE(SUM(Table1[Value]),DATESBETWEEN(Table1[YYYYMM],Max(Table1[YYYYMM]),MINX(DATEADD(All(Table1[YYYYMM]),5,MONTH),Table1[YYYYMM]))),6)

DivideValue = DIVIDE([Lookup Table2],[Average 6 months])

My datavalues are a little different from the one you shared. But this should work.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[RDzeketey]

Hi, @harshnathani ,

Thank you

Your method looks much simpler but the 6 month average only works for the first line (26,417). The second line 6 mo average does not compute correctly per your numbers. It should be a rolling 6 months(including current month)  calculation. PLease see below.

THank you

[harshnathani]

Hi @RDzeketey ,

Made some changes and I think it shold work fine now.

Average 6 months =

var _Mx = MINX(DATEADD(Table1[YYYYMM],6,MONTH),Table1[YYYYMM])

var _a = MAx(Table1[YYYYMM])

RETURN

DIVIDE(CALCULATE(SUM(Table1[Value]),Filter(ALL(Table1),Table1[YYYYMM] >= _a && Table1[YYYYMM] < _Mx)),6)

Lookup Table2 = LOOKUPVALUE(Table2[Value],Table2[YYYYMM],MAx(Table1[YYYYMM]))

DivideValue = DIVIDE([Lookup Table2],[Average 6 months])

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

Hi @RDzeketey ,

I have a question though, just a lil curious.

Rolling 6 months is calculated on average of 6 months before that particular date. But you seem to be doing it the opposite i.e calculating avg 6 months after a date. Any specific reasons.

Just curious.

Regards,

HN

[RDzeketey]

Apologies, I meant forward looking. Current month plus 5. Thank you

[stevedep]

Can you share some more info on why my method is not working? Perhaps we can make it work?

[RDzeketey]

Hi, @stevedep 

Thank you for your help. I realized that it was partially my fault that it was showing that way because I had 5 products in the same table, so that is why my RANKING was jumping in increments of 5.

I am not able to attach the file so here is another screenshot. I got values, but they are not what I expect to get when I test it in excel.

Thank you

[stevedep]

Van you share your excel logic? And an example with a difference?

[RDzeketey]

Hi, 

Hope this makes more sense.

Thank you

[stevedep]

Hi,

2 interesting findings;

1. My calculation was missing a 'VALUES' summary.

2. Your spreadsheet does not seem to calculate the average correctly? For the first 6 months it should be 3879,68?

See;

Power BI file availabe here, Excelfile available here. 

Hope it helps.

Kind regards,

Steve. 

[RDzeketey]

Thanks, @stevedep 

The Average works. However, for the Ratio, the math does not work.

6,472/3,879.68 = 1.69, not 1.62.

It looks to be taking the average of the entire table 1 (3987.22); and hence 6,472/3,987.22 = 1.62

I am not sure how to fix that.

Thank you

[stevedep]

Ah, I see now. Changed:

ratio = 
var filtervar = FILTER(ALLSELECTED(Table1[YYYYMM]);[RANK] >= 1 && [RANK] <= 6)
var selectedvalue = SELECTEDVALUE(Table2[Value])
var v_avg = CALCULATE(CALCULATE(
AVERAGE(Table1[Value]));filtervar) 
return
selectedvalue 
/v_avg

Giving the expected result:

File available here.

Kind regards,

Steve. 

[stevedep · Accepted solution]

This is the adjusted code:

AverageNext6Months = 
var v_dates =  DATESINPERIOD( DateNew[Date]; MAX( DateNew[Date] ); +5;  MONTH )
return
AVERAGEX(v_dates;CALCULATE(SUM(Forecast[Forecast Value])))

Ratio:

Ratio = MAX(OH[OH Value]) / [AverageNext6Months] 

[stevedep]

Hi,

This should work, perhaps not the most elegant solution, but this should work. 

2 steps, first a ranking on the monthnumber, second calculate the average for the next 6 months and then do the division. 

rank formula:

RANK = var sv = SELECTEDVALUE(Table2[YYYYMM])
var sv_tbl1 = SELECTEDVALUE(Table1[YYYYMM])
var rankk = CALCULATE(COUNTROWS(Table1);FILTER(ALL(Table1[YYYYMM]);Table1[YYYYMM] >= sv && Table1[YYYYMM] <= sv_tbl1 ))
return
rankk

step 2, the calculation:

average = 
var filtervar = FILTER(Table1;[RANK] >= 1 && [RANK] <= 6)
var selectedvalue = SELECTEDVALUE(Table2[Value])

return
selectedvalue / 
CALCULATE(
AVERAGE(Table1[Value]);filtervar) 

Result is as expected:

Link to the Power BI file is here.

Please accept as solution if this works for you.

Kind regards,

Steve. 

[RDzeketey]

When I use the RANK, this is the output I get and then the next part of the formula does not produce any values.

[stevedep]

Can you share a file or full screenshot with your selections and attributes?