Forum Discussion
Calculate Sumx aggregation - filter context - issue
Dear community,
I have following data in Tbl1:
| TransID | Product | Volume | Price per piece |
| 1 | A | 10 | 1 |
| 2 | B | 20 | 1,1 |
| 3 | B | 40 | 1,2 |
I want to have a measure, showing the total of 70 (sum of vol*price for B-products) in the rows of B (row 2 and 3):
Target:
| TransID | Product | Volume | Price per piece | M_test |
| 1 | A | 10 | 1 | |
| 2 | B | 20 | 1,1 | 70 |
| 3 | B | 40 | 1,2 | 70 |
Why is following measure not giving out these 70 for the B-products?
From my understanding, as TransID is relaxed/not restrictive, the data for the sumx computation should be both row 2 and row 3. Why is it not aggregating - similar to the behavior when I relax all filters? (When working with Keepfilters, I get it to do the correct calculation, but I like to understand, why my formula is not functioning?)
Thank you for every hint!
not working:
Why not 70 at lines of product B?
Install DAXDebugOutput and learn how to use EVALUATEANDLOG
5 Replies
You forgot to remove the TransID filter
M_Test = CALCULATE(sumx(Tbl1,[Volume]*[Price per piece]),KEEPFILTERS(Tbl1[Product]="B"),REMOVEFILTERS(Tbl1[TransID]))lbendlin : Thanks for your reply. I am still curious, what Power BI is doing in background that following formular is not functioning to get the 70 result in each line of product B:
M_test =CALCULATE(SUMX(Tbl1, Tbl1[Volume] * Tbl1[Price per piece]),ALL(Tbl1[TransID]),FILTER(Tbl1, Tbl1[Product] = "B"))
even if I also relax/removefilter from Tbl1 for Product all(Tbl1[Product]), I still get not the desired result. It seems that Power BI is not doing the aggregation (even though it does when I fully remove all filters from the table.?). I searched the internet / geminis, etc.. but not fully sure. Some outer / inner filter context might be a reason, I do not understand why the removal of filters and the sum does not work here.Maybe anyone has information on what Power BI is doing it.Install DAXDebugOutput and learn how to use EVALUATEANDLOG
Hi BB912554,
Thank you lbendlin for your response and it works.
Here's the DAX measure that you might find helpful:
M_test =
CALCULATE(
SUMX(
FILTER(ALL(Tbl1), Tbl1[Product] = "B"),
Tbl1[Volume] * Tbl1[Price per piece]
)
)The DAX measure provided by the super user is also working correctly and fulfills the requirement as expected.
I'm attaching the .pbix file with the working DAX and sample data, so you can explore the logic and test it yourself directly.
HI BB912554
As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?
And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.
Your understanding and patience will be appreciated.
