Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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 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]))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |