March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello I hope all are well.
I have been trying to build a measure but I feel that it is getting very complicated.
The intention of the measure is that of a SUMIFS, which sum LE Volume metric, having the Category dimension as a condition. I have already obtained a correct result but as long as I do not apply any filter, it is a static result that does not change according to the filters applied to the report. My DAX code is the following:
SUMIFS = CALCULATE(
SUM(Worksheet[LE Volume]),
Worksheet[Region]=Worksheet[Region],
ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category], Worksheet[Region]))
The measurement should be displayed in a large table with different dimensions like the following:
The intention is to be able to filter a table, by region and that the results of that SUMIFS are adjusted according to the region filter applied.
The intention is to distribute the total of my LE Volume metric, for each category and that changes according to the selected Region of the report filter
Share PBI File
Solved! Go to Solution.
@mlozano you I think you want this:
SUMIFS =
CALCULATE(
SUM(Worksheet[LE Volume]),
REMOVEFILTERS(Worksheet),
VALUES(Worksheet[CAT])
)
Or this:
SUMIFS =
CALCULATE(
SUM(Worksheet[LE Volume]),
REMOVEFILTERS(Worksheet),
VALUES(Worksheet[Category])
)
You have 2 columns there for cateogry..
@mlozano just to be sure, if you want a measure that will be responsive to all the filters, you just need to write
SUM(Worksheet[LE Volume])
This line: Worksheet[Region]=Worksheet[Region] is definetly not needed or just written wrong, and not sure why you wrote the 3rd one.
Can you try the simple version and reply what is missing in your result.
What happens is that by including SUM(Worksheet[LE Volume]) in the table there is a distribution of the total but row by row and it is not what I need. I need the total LE Volume distribution to be calculated for each category and this to change based on the region filter.
It actually works without the part you mention, but it's still a static value that doesn't change depending on the applied filter, I know it's because of the ALLEXCEPT function, the question would be how can I adjust the code so that the distribution of the total LE VOLUME per category becomes dynamic whenever i filter by region.
@mlozano you I think you want this:
SUMIFS =
CALCULATE(
SUM(Worksheet[LE Volume]),
REMOVEFILTERS(Worksheet),
VALUES(Worksheet[CAT])
)
Or this:
SUMIFS =
CALCULATE(
SUM(Worksheet[LE Volume]),
REMOVEFILTERS(Worksheet),
VALUES(Worksheet[Category])
)
You have 2 columns there for cateogry..
Thank you friend, you can't imagine how you helped me.
@mlozano my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |