topic Re: Filter for value and divide by average of a range in DAX Commands and Tips

Filter for value and divide by average of a range

RDzeketey — Fri, 29 May 2020 21:09:05 GMT

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

Re: Filter for value and divide by average of a range

stevedep — Sat, 30 May 2020 08:58:18 GMT

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.

Re: Filter for value and divide by average of a range

RDzeketey — Mon, 01 Jun 2020 16:23:22 GMT

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

Re: Filter for value and divide by average of a range

stevedep — Mon, 01 Jun 2020 16:42:25 GMT

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

Re: Filter for value and divide by average of a range

Anonymous — Mon, 01 Jun 2020 18:15:39 GMT

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)

Re: Filter for value and divide by average of a range

RDzeketey — Mon, 01 Jun 2020 19:57:08 GMT

Hi, Anonymous ,

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

Re: Filter for value and divide by average of a range

stevedep — Mon, 01 Jun 2020 20:13:26 GMT

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

Re: Filter for value and divide by average of a range

RDzeketey — Mon, 01 Jun 2020 20:58:57 GMT

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

Re: Filter for value and divide by average of a range

stevedep — Mon, 01 Jun 2020 21:02:50 GMT

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

Re: Filter for value and divide by average of a range

RDzeketey — Mon, 01 Jun 2020 21:17:10 GMT

Hi,

Hope this makes more sense.

Thank you

Re: Filter for value and divide by average of a range

stevedep — Tue, 02 Jun 2020 06:15:32 GMT

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.

Re: Filter for value and divide by average of a range

Anonymous — Tue, 02 Jun 2020 07:29:46 GMT

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)

Re: Filter for value and divide by average of a range

Anonymous — Tue, 02 Jun 2020 07:32:28 GMT

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,

Re: Filter for value and divide by average of a range

RDzeketey — Tue, 02 Jun 2020 13:46:16 GMT

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

Re: Filter for value and divide by average of a range

RDzeketey — Tue, 02 Jun 2020 15:34:35 GMT

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

Re: Filter for value and divide by average of a range

stevedep — Tue, 02 Jun 2020 15:56:02 GMT

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.

Re: Filter for value and divide by average of a range

stevedep — Wed, 03 Jun 2020 17:41:01 GMT

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]