How to use multiple filters in powerpivot?

xilef

New Member
Joined
Nov 10, 2016
Messages
4
Hello!


I'm new at powerpivot and i have a hard time resolving the following problem.


I have a table with the following columns





seller name


product name


sales period (month)


number sold


Sales amount





I want to make a measure that answers for this question: The last period my seller sold the product X, how much was the sales price?


I tried a number of ways, but i dont get the filters right.


Last sold period= calculate(max('table[sales period]);'table'[product name]="X")


Sales amount for X last period= calculate(sum('table'[sales amount]); filter('table';'table'[sales period]='table'[Last sold period]);'table'[product name]="X")





In the last formula - something goes wrong. I understand that i cannot filter in the way that I do - but i dont know how to make it better. Any tips or suggestions?








Thank you!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
There is complex context transition stuff that could be tripping you up. If you build a sample workbook and post a link i will take a look for you.
 
Upvote 0
Thank you Matt for your willing to help! I am so thankful!

I uploaded a example file here:


https://volafile.io/r/BFrb0oG
 
Upvote 0
Hi xilef,

Sorry for my slow reply. I have been very busy travelling and attending conferences and haven't had a chance to download the file and take a look. I definitely hadn't forgotten, in fact I only saw the bump when I came in today to provide you an answer. Anyway, try this

=CALCULATE(
CALCULATE(AVERAGE(Tabell1[Sales amount]),
LASTNONBLANK(Tabell1[Period],1)
),Tabell1[Product name] = "X"
)
 
Upvote 0
Hi xilef,

Sorry for my slow reply. I have been very busy travelling and attending conferences and haven't had a chance to download the file and take a look. I definitely hadn't forgotten, in fact I only saw the bump when I came in today to provide you an answer. Anyway, try this

=CALCULATE(
CALCULATE(AVERAGE(Tabell1[Sales amount]),
LASTNONBLANK(Tabell1[Period],1)
),Tabell1[Product name] = "X"
)

Thank you so much Matt!! Now it works like a charm! I did not think of the possibility to first filter out the product, and inside that do the other filtering!
Thank you so much for your time!
 
Upvote 0
In some other languages, nesting can be inefficient. In DAX it is very efficient, because you can first filter out the data you don't need, and then operate over a reduced set of data.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top