cancel
Showing results for
Did you mean: 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 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]?

1 ACCEPTED SOLUTION  Super User

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.

6 REPLIES 6  Super User

@bamber

You expression should be :

``````Expression =

SUMX(
Table2,
IF(
Table2[Metric] > Table2[Cap],
Table2[Cap],
Table2[Metric]
)
)`````` My sample data: Did I answer your question? Mark my post as a solution! and hit thumbs up 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 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.  Super User

@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 Frequent Visitor

Sure. That would be:

 Dim A Sum of Expression A 241 B 131  Super User

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. Frequent Visitor

@VahidDM
Works perfectly. Thanks! Announcements #### 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. #### 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! #### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February. #### 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
Users online (2,278)