Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
mlozano
Helper II
Helper II

Help with DAX that calculates the total by category

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:

 

mlozano_1-1655131726440.png

 

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 

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

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

SpartaBI
Community Champion
Community Champion

@mlozano You mean you want the total by category?

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
Community Champion
Community Champion

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


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

SpartaBI
Community Champion
Community Champion

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.