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

DAX CALCULATE ERROR

Hello, I have the following problem.

 

I have a measure built in DAX called Share % SKU ACT Volume, it is the percentage participation that each SKU has in the total ACT Volume, with that measure I have no problem.

 

Now I need to build a SUMIF that calculates the Share % SKU ACT Volume grouped by category for which I am using CALCULATE. So far I managed to build a code that brings me the correct result but when I apply the region filter or any filter, the result is static.

 

my code is as follows:

 

SUM of SKU ACT Volume Share % by Category Static = CALCULATE(Worksheet[SKU ACT Volume % Share], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))

 

I understand that the result is static because I am using ALLEXCEPT, so he tried to set my CALCULATE in such a way that my result changes and is correct according to the applied filters. 

 

So I come up with the following code:

 

SUM of Share % SKU ACT Volume by category = CALCULATE(Worksheet[Share % SKU ACT Volume], ALLSELECTED(Worksheet), VALUES(Worksheet[Month]), VALUES(Worksheet[Category]))

 

The result I get is incorrect, my intention is to get to the result of the measure SUM of SKU ACT Volume Share % by Category Static but that the result changes according to the applied filters.

 

How can I achieve it?

 

Share PBI File PBI FILE 

1 ACCEPTED SOLUTION
mlozano
Helper II
Helper II

I solved it like this:

 

 

VAR Numerator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet), VALUES(Worksheet[Month]),VALUES(Worksheet[Category]))

VAR Denominator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet))

RETURN

DIVIDE(Numerator, Denominator)

 

 

View solution in original post

5 REPLIES 5
mlozano
Helper II
Helper II

I solved it like this:

 

 

VAR Numerator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet), VALUES(Worksheet[Month]),VALUES(Worksheet[Category]))

VAR Denominator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet))

RETURN

DIVIDE(Numerator, Denominator)

 

 

vapid128
Solution Specialist
Solution Specialist

you can't calculate a colnum, it need to be a measure, like sum the colnum or ave the colnum

 

SUM of SKU ACT Volume Share % by Category Static = CALCULATE(Sum(Worksheet[SKU ACT Volume % Share]), ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))

 

 

A calculated column would contain static values ​​and I need my result to change as I apply filters on the report.

 

To add the measure I must create another measure with a SUMX to force the context of the row but before doing so I must obtain the correct values ​​from my calculate

vapid128
Solution Specialist
Solution Specialist

OKOK

I download you file.

So... you want calculate ACT Volume Share % for each SKU ID base on colnum Month and colnum Category. And sum all those number become 100%

 

like this

vapid128_1-1655341203517.pngvapid128_2-1655341225066.png

 

Measure = AVERAGEX(
    VALUES(Worksheet[index]),
    CALCULATE(
        DIVIDE(
            SUM([ACT Volume]),
            CALCULATE(
                SUM([ACT Volume]),
                REMOVEFILTERS(Worksheet[SKU ID])
            )
        )
    )
)
index = [Month]&[Category]

Thanks for your answer. It is not what I need, the participation percentage is already calculated, which is the Share % SKU ACT Volume measure, now what I have to do is, that measure, which is my participation percentage, include it in a calculate that groups it by month and category .

 

So if you can see with the following code:

SUM of Share % SKU ACT Volume by category Static = CALCULATE(Worksheet[Share % SKU ACT Volume], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))

 

Result

 

Without filters

mlozano_0-1655343169023.png

It has the result that I expect, what happens is that this result is not dynamic since I apply the region filter and it is the same all the time, what I need is to get that result and that it fits to the granularity defined by the filters.

 

With filters

mlozano_1-1655343224943.png

 

The expected result would be the distribution of 100% that represents the total of my participation among my four categories and that this be dynamic every time I change the region filter.

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.