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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bamber
Frequent Visitor

Sum of expression to respect LOD

Hi,

I can't figure out a DAX statement that would correctly sum results of another expression.
I've built a table visual, in which [Expression] is a simple IF statement which checks if sum of [Metric] is lower or equal than a set maximum value (i.e. [Cap]) and then returns either the sum or [Cap]

 

TABLE VISUAL 1   
Dim ADim BSum of MetricCapExpression
AA1353
AA2454
AA3755
AA410055
BB1121212
BB2141212
BB37127
BB4251212


The Expression goes like this:

Expression =
IF
(
SUM([Metric]) > MIN([Cap]),
MIN('DataTable'[Cap]),
SUM([Metric])
)

Now I would like to build another table visual that would correctly sum [Expression] by Dim A and would be possible to filter by other dimensions in the Data Tabe (Dim C, D, E... that are not used in the visual)

TABLE VISUAL 2
Dim ASum of Expression
A17
B43

 

I tried CALCULATE with SUMX and ALLEXCEPT on [Dim A] and [Dim B] but results make no sense.
In Tableau I would probably use something like SUM({FIXED [Dim A],[Dim B]:[Expression]}), but I'm struggling to replicate this logic in DAX.


What would be the correct DAX for [Expression] and [Sum of Expression]?

1 ACCEPTED SOLUTION

Hi @bamber 

 

Try this measure:

Expression = 
VAR _SumTable =
    SUMMARIZE (
        'DataTable',
        'DataTable'[Dim A],
        'DataTable'[Dim B],
        "Expression",
            IF (
                CALCULATE (
                    SUM ( 'DataTable'[Metric] ),
                    ALLEXCEPT ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B] )
                )
                    < MIN ( 'DataTable'[Cap] ),
                CALCULATE (
                    SUM ( 'DataTable'[Metric] ),
                    ALLEXCEPT ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B] )
                ),
                MIN ( 'DataTable'[Cap] )
            )
    )
RETURN
    SUMX ( _SumTable, [Expression] )

 

Output:

VahidDM_0-1631499363439.png

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@bamber 

You expression should be :

Expression = 

SUMX(
    Table2,
    IF(
        Table2[Metric] > Table2[Cap],
        Table2[Cap],
        Table2[Metric]   
    )        
)

Fowmy_0-1631445024208.png

My sample data: 

Fowmy_1-1631445047093.png

 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

bamber
Frequent Visitor

Hi @Fowmy , thanks for replying so quickly.

Your solution indeed works with a much simplified dataset, however, my data is a bit more complicated.
Each [Dim B] has its [Cap] value and there are other dimensions - that's why I used SUM and MIN to evaluate [Expression] in my initial approach.
The actual dataset looks more like that (sorry, I can't share real data; I just added one more dimension to simulate granularity):

Dim ADim BDim CMetricCap
AA1C1315
AA2C24100
AA3C3720
AA4C1100200
BB1C21222
BB2C31413
BB3C1750
BB4C22590
AA1C3315
AA2C14100
AA3C2720
AA4C3100200
BB1C11222
BB2C21413
BB3C3750
BB4C12590
AA1C2315
AA2C34100
AA3C1720
AA4C2100200
BB1C31222
BB2C11413
BB3C2750
BB4C32590


With dataset like this, the approach with SUMX of an IF statement doesn't return correct results.

@bamber 

Can you share the expected result based on this data set?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

bamber
Frequent Visitor

Sure. That would be:

Dim ASum of Expression
A241
B131

Hi @bamber 

 

Try this measure:

Expression = 
VAR _SumTable =
    SUMMARIZE (
        'DataTable',
        'DataTable'[Dim A],
        'DataTable'[Dim B],
        "Expression",
            IF (
                CALCULATE (
                    SUM ( 'DataTable'[Metric] ),
                    ALLEXCEPT ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B] )
                )
                    < MIN ( 'DataTable'[Cap] ),
                CALCULATE (
                    SUM ( 'DataTable'[Metric] ),
                    ALLEXCEPT ( 'DataTable', 'DataTable'[Dim A], 'DataTable'[Dim B] )
                ),
                MIN ( 'DataTable'[Cap] )
            )
    )
RETURN
    SUMX ( _SumTable, [Expression] )

 

Output:

VahidDM_0-1631499363439.png

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

@VahidDM 
Works perfectly. Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors