cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Filter for value and divide by average of a range

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.

1 ACCEPTED SOLUTION
Memorable Member

``````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] ``

Proud to be a Super User!

16 REPLIES 16
Community Champion

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 =

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)

Frequent Visitor

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

Community Champion

Hi @RDzeketey ,

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

Average 6 months =
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)
Community Champion

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

Frequent Visitor

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

Memorable Member

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

Proud to be a Super User!

Frequent Visitor

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

Memorable Member

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

Proud to be a Super User!

Frequent Visitor

Hi,

Hope this makes more sense.

Thank you

Memorable Member

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.

Proud to be a Super User!

Frequent Visitor

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

Memorable Member

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.

Proud to be a Super User!

Memorable Member

``````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] ``

Proud to be a Super User!

Memorable Member

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.

Proud to be a Super User!

Frequent Visitor

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

Memorable Member

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

Proud to be a Super User!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors