cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors