Forum Discussion
Error Calculate
Hello, again looking for help in the community.
I find myself building a SUMIFS, which sums the LE Volume field, grouped by region and category.
The DAX I have at the moment is the following:
SUMIFS =
CALCULATE(
SUM(Worksheet[LE Volume]),
Worksheet[Region]=Worksheet[Region],
ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))
The result is:
without filters
The result i get is correct as long as you do not apply any filter in the report. When I try to apply a filter by region, the result remains static and is not recalculated according to the selected region
with the filter "Reg Centro"
how can I adjust my CALCULATE so that the result is dynamic and changes according to the selected region?
I share power bi file
I raised it in the following way and achieved the expected result.
Do you think it is a good alternative?
CALCULATE( SUM(Worksheet[LE Volume]), KEEPFILTERS(Worksheet[Month]=Worksheet[Month]), KEEPFILTERS(Worksheet[Category]=Worksheet[Category]))
4 Replies
ALLEXCEPT removes all filters except the ones added as parameters. So that's why it doesn't respond to a change in Region.
What is it you are trying to show in the visual? (because Abril is the only month in the data) - it looks like a simple SUM is what's required but maybe you can explain further
I only want to find the sum of LE Volume grouped by month and category, that when filtering by region said calculation shows the result corresponding to each category according to the applied filters. The month is included because at some point the database will have more data per month.
ALLEXCEPT is the function that I have come closest to the result with, but even including the region field as a filter argument, my calculation remains static even when applying filters.
I raised it in the following way and achieved the expected result.
Do you think it is a good alternative?
CALCULATE( SUM(Worksheet[LE Volume]), KEEPFILTERS(Worksheet[Month]=Worksheet[Month]), KEEPFILTERS(Worksheet[Category]=Worksheet[Category]))
Looks like you are over-thinking it.
Just use a simple SUM. If you want a better model ,put Month and Category in dimension tables - linked 1:m with the main fact table
