Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
1 year ago
Solved

DAX Help with tricky CALCULATE

Hi Experts!

 

My fact table looks like this:

EIDAIDVAL
1180
120
13100
210
22170
23200
31270
32270
33300
41380
43400

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:

EIDPerc
180%
285%
3180%
495%

However, Perc for EID = 3 is wrong. What I want is a Measure that produces the following matrix:

EIDPerc
180%
285%
390%
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

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