Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]?
Solved! Go to 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
You expression should be :
Expression =
SUMX(
Table2,
IF(
Table2[Metric] > Table2[Cap],
Table2[Cap],
Table2[Metric]
)
)
My sample data:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@bamber
Can you share the expected result based on this data set?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Sure. That would be:
Dim A | Sum of Expression |
A | 241 |
B | 131 |
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✌️!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |