Help with DAX that calculates the total by category

[mlozano]

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

PBI FILE 

[SpartaBI]

@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.

[mlozano]

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.

[SpartaBI]

@mlozano You mean you want the total by category?

[mlozano]

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.

[SpartaBI · Accepted 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..

Showcase Report – Contoso By SpartaBI

      

[mlozano]

Thank you friend, you can't imagine how you helped me.

[SpartaBI]

@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 🙂