Using multiple criteria to sum a column in the same table

[chaserracer83]

Hello there, I have a table in PowerPivot (let's just call it "table") and I need to create a calculated field that sums the value of field "currency" based on criteria within the table:

sum(table[currency])

Where

table[age] >9

and

table[age] < value of table[age] for the current row

and

table[product] = value of table[product] for the current row


I've found some posts with somewhat similar questions, but nothing that seems to work for this. I've tried Earlier(), SumX() with Filter()... I just keep getting errors, but I may not being using these correctly.

Thank you for you help.
Chase

 

[anvg]

Hi
Maybe

=CALCULATE(SUM(table[currency], FILTER(table, table[age]>9 && table[age]<EARLIER(table[age]) && table[product]=EARLIER(table[product])))

https://www.sqlbi.com/articles/understanding-context-transition/
Regards,

 

[chaserracer83]

Thank you for the response. You actually answered my last post!... I didn't get an error which is a step in the right direction, but your equation is missing some filters and I can't figure out how to add them without getting an error (specifically only summing matching products, and age less than the current row). Also, did you intend to have a closing parenthesis for sum or does the filter stay within that function? is the part after the "&&" the beginning of the finding [age] less than the current row, or is that needed as part of the first filter? Sorry for all the questions. Thank you again.

 

[chaserracer83]

Let me add how I put the formula in:

=CALCULATE(SUM(table[currency]), filter(table, table[age]>9 && table[age])

This produces the same result for every row, so I'm not sure if it is filtering anything

 

[anvg]

Could you upload your example file (to dropbox or else) for a test?

 

[chaserracer83]

Sure thing. I just created/uploaded an example file to the dropbox link below.

I included a DAX formula for how I would have imagined this working, but it just gives a blank result.

I read through the transition article you sent in your first reply. It seems like a good source of info for this particular subject, but I still can't seem to get there.

Thank you so much for your help.

Chase

Dropbox link:
https://www.dropbox.com/s/7hsy93irjjsun9u/example%20file.xlsx?dl=0

 

[chaserracer83]

Hey there buddy, did I lose you?

 

[anvg]

I am sorry, I haven't seen that the forum ate a part of my formula in #2 post.
I uploaded your example file into Zippyshare.com - example file.xlsx
Regards, Andrey.
P. S. Don't worry, be happy!

 

[chaserracer83]

That worked beautifully. Thank you again for all your help. I couldn't have gotten there on my own.

And in case anyone has the same issue. The formula in the spreadsheet is:

=CALCULATE(SUM(Forecast_Template[Expected_Unit_Loss_(No_Seasonal_Adj)]),
FILTER(Forecast_Template,
Forecast_Template[MOB]>9
&& Forecast_Template[MOB]<EARLIER(Forecast_Template[MOB])
&& Forecast_Template[Key_(Vintage_LOB_LFS)] = EARLIER(Forecast_Template[Key_(Vintage_LOB_LFS)])
)
)