DAX Help with tricky CALCULATE

[WishAskedSooner]

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!

[Tutu_in_YYC · Accepted solution]

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
   )
)

[WishAskedSooner]

@Tutu_in_YYC 

Wow! That worked perfectly and is beautiful DAX code. Exactly what I needed. Many Kudos!

[Tutu_in_YYC]

Glad to hear!