Sum of expression to respect LOD

[bamber]

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 A	Dim B	Sum of Metric	Cap	Expression
A	A1	3	5	3
A	A2	4	5	4
A	A3	7	5	5
A	A4	100	5	5
B	B1	12	12	12
B	B2	14	12	12
B	B3	7	12	7
B	B4	25	12	12

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 A	Sum of Expression
A	17
B	43

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]?

[Fowmy]

@bamber 

You expression should be :

Expression = 

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

My sample data: 

[bamber]

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 A	Dim B	Dim C	Metric	Cap
A	A1	C1	3	15
A	A2	C2	4	100
A	A3	C3	7	20
A	A4	C1	100	200
B	B1	C2	12	22
B	B2	C3	14	13
B	B3	C1	7	50
B	B4	C2	25	90
A	A1	C3	3	15
A	A2	C1	4	100
A	A3	C2	7	20
A	A4	C3	100	200
B	B1	C1	12	22
B	B2	C2	14	13
B	B3	C3	7	50
B	B4	C1	25	90
A	A1	C2	3	15
A	A2	C3	4	100
A	A3	C1	7	20
A	A4	C2	100	200
B	B1	C3	12	22
B	B2	C1	14	13
B	B3	C2	7	50
B	B4	C3	25	90

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

[Fowmy]

@bamber 

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

[bamber]

Sure. That would be:

Dim A	Sum of Expression
A	241
B	131

[VahidDM · 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:

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

Appreciate your Kudos✌️!!

[bamber]

@VahidDM 
Works perfectly. Thanks!