Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
1 year ago
Solved

Again a Tricky CALCULATE

Hi Experts!

 

This is a follow up from a similar post.

 

Here is my Fact table:

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

400

I have a Report with EID in a slicer and a Matrix with AID in the Rows with the Sum of VAL loaded in the Values. I am trying to author a Measure that returns the Sum of AID = 1 but exludes or returns zero for each EID when AID = 2 and is greater than zero. Here is my measure so far:

SUM AID 1 = 
    VAR _AID2 = CALCULATE(SUM('Table'[VAL]), 'Table'[AID] = 2)
    VAR _Sum =
        IF(
            _AID2 > 0,
            0,
            CALCULATE(SUM('Table'[VAL]), ALLEXCEPT('Table', 'Table'[EID]), 'Table'[AID] = 1)
        )
    RETURN
        _Sum

If I click through each EID singly, I get the proper result. Below are two examples that are correct. In the first, EID = 1 and since AID = 2 is zero, the Sum of AID 1 returns the proper value.

In this example, EID = 3 and because AID = 2 is greater than zero, the sum of AID = 1 should be and is zero.

As I stated, so far so good. However, the measure fails when I start choosing more than one EID. For example, if I choose EID 1 and 3, SUM AID 1 returns zero when it should return 80 like above.

Hopefully, that makes sense. Thanks in advance for the the help!

Need Help
  • TomMartens's avatar
    TomMartens
    1 year ago

    Hey WishAskedSooner ,

     

    check if this measure returns the expected result:

    Tom = 
SUMX(
    VALUES( 'Table'[EID]),

    VAR _AID2 = CALCULATE(SUM('Table'[VAL]), 'Table'[AID] = 2)
    VAR _Sum =
        IF(
            _AID2 > 0,
            0,
            CALCULATE(SUM('Table'[VAL]), ALLEXCEPT('Table', 'Table'[EID]), 'Table'[AID] = 1)
        )
    RETURN
        _Sum
)

    How it looks in the Matrix if the EIDs 1 and 3 are selected:

    Using the iterator function SUMX returns the result for each selected EID and is adding up the single results.

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi WishAskedSooner ,

     

    To solve the issue where your measure incorrectly returns zero when multiple EIDs are selected, you can use a SUMX loop over each EID and conditionally calculate the value for AID = 1 only if the corresponding value for AID = 2 is zero. This way, the logic is applied per EID, not across all selected EIDs combined.

    You can use the following DAX formula:

    SUM AID 1 = 
SUMX(
    VALUES('Table'[EID]),
    VAR _AID2_Value = 
        CALCULATE(
            SUM('Table'[VAL]),
            ALLEXCEPT('Table', 'Table'[EID]),
            'Table'[AID] = 2
        )
    VAR _AID1_Value =
        CALCULATE(
            SUM('Table'[VAL]),
            ALLEXCEPT('Table', 'Table'[EID]),
            'Table'[AID] = 1
        )
    RETURN
        IF(_AID2_Value > 0, 0, _AID1_Value)
)

    This measure first creates a virtual table of all selected EIDs using VALUES('Table'[EID]), then iterates over each EID. For each EID, it calculates the sum of VAL where AID = 2 and where AID = 1. If the AID 2 value is greater than zero, it returns zero; otherwise, it returns the AID 1 value. Finally, SUMX adds up the results for all selected EIDs, giving you the expected result even when multiple EIDs are selected.

     

    Best regards,

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

    Hey WishAskedSooner ,

     

    check if this measure returns the expected result:

    Tom = 
SUMX(
    VALUES( 'Table'[EID]),

    VAR _AID2 = CALCULATE(SUM('Table'[VAL]), 'Table'[AID] = 2)
    VAR _Sum =
        IF(
            _AID2 > 0,
            0,
            CALCULATE(SUM('Table'[VAL]), ALLEXCEPT('Table', 'Table'[EID]), 'Table'[AID] = 1)
        )
    RETURN
        _Sum
)

    How it looks in the Matrix if the EIDs 1 and 3 are selected:

    Using the iterator function SUMX returns the result for each selected EID and is adding up the single results.

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

    • WishAskedSooner's avatar
      WishAskedSooner
      Continued Contributor
      1 year ago

      TomMartens and DataNinja777 ,

       

      Thank you both for your quick answers! I tested them both, and they both work as expected. And I didn't see any differences between the results. So I have marked both as solutions.

       

      I have already implemented them into my actual model and SUMX was indeed the solution that I needed. I don't have much experience with SUMX as I use CALCULATE almost exclusively, so I was a little hesitant on how to proceed.

       

      Many kudos to you both!

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi WishAskedSooner ,

     

    To solve the issue where your measure incorrectly returns zero when multiple EIDs are selected, you can use a SUMX loop over each EID and conditionally calculate the value for AID = 1 only if the corresponding value for AID = 2 is zero. This way, the logic is applied per EID, not across all selected EIDs combined.

    You can use the following DAX formula:

    SUM AID 1 = 
SUMX(
    VALUES('Table'[EID]),
    VAR _AID2_Value = 
        CALCULATE(
            SUM('Table'[VAL]),
            ALLEXCEPT('Table', 'Table'[EID]),
            'Table'[AID] = 2
        )
    VAR _AID1_Value =
        CALCULATE(
            SUM('Table'[VAL]),
            ALLEXCEPT('Table', 'Table'[EID]),
            'Table'[AID] = 1
        )
    RETURN
        IF(_AID2_Value > 0, 0, _AID1_Value)
)

    This measure first creates a virtual table of all selected EIDs using VALUES('Table'[EID]), then iterates over each EID. For each EID, it calculates the sum of VAL where AID = 2 and where AID = 1. If the AID 2 value is greater than zero, it returns zero; otherwise, it returns the AID 1 value. Finally, SUMX adds up the results for all selected EIDs, giving you the expected result even when multiple EIDs are selected.

     

    Best regards,