Forum Discussion
DAX Help with tricky CALCULATE
Hi Experts!
My fact table looks like this:
| EID | AID | VAL |
| 1 | 1 | 80 |
| 1 | 2 | 0 |
| 1 | 3 | 100 |
| 2 | 1 | 0 |
| 2 | 2 | 170 |
| 2 | 3 | 200 |
| 3 | 1 | 270 |
| 3 | 2 | 270 |
| 3 | 3 | 300 |
| 4 | 1 | 380 |
| 4 | 3 | 400 |
Currently, this is my Measure:
DIVIDE(
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] IN {1, 2}
),
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 3
)
)
which results in the following matrix visual:
| EID | Perc |
| 1 | 80% |
| 2 | 85% |
| 3 | 180% |
| 4 | 95% |
However, Perc for EID = 3 is wrong. What I want is a Measure that produces the following matrix:
| EID | Perc |
| 1 | 80% |
| 2 | 85% |
| 3 | 90% |
| 4 | 95% |
The complication comes in with AID = 2 which can sometimes be either zero (e.g. EID = 1), greater than zero (EID = 2 and 3), or missing (e.g. EID -= 4).
So I need a measure that performs like the pseudo-code below where if the VAL is greater than zero when AID = 2 and not missing then calculate the percentage with AID = 2 only. Otherwise, calculate the percentage with AID = 1 only.
IF(
'Fact'[VAL] > 0 WHEN 'Fact'[AID] = 2, // The same row
DIVIDE(
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 2
),
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 3
)
),
DIVIDE(
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 1
),
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 3
)
)
)Thanks in advance for the help!
Try this:
Measure =
DIVIDE(
VAR _AID2 =
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 2
)
RETURN
IF(
_AID2 > 0,
_AID2,
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 1
)
)
,
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 3
)
)
3 Replies
- Tutu_in_YYCSuper User
Try this:
Measure =
DIVIDE(
VAR _AID2 =
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 2
)
RETURN
IF(
_AID2 > 0,
_AID2,
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 1
)
)
,
CALCULATE(
SUM('Fact'[VAL]),
'Fact'[AID] = 3
)
)- WishAskedSoonerContinued Contributor
Wow! That worked perfectly and is beautiful DAX code. Exactly what I needed. Many Kudos!
- Tutu_in_YYCSuper User
Glad to hear!