Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Substract value based on 2 filters on the same dimension

Dear all,'

 

I have an issue trying to calculate the difference between 2 entities using 2 slicers.

 

database: table with 4 entities with associated amounts.

d_entity: table with entity description and hierarchy

d_entity_bis: reference of d_entity

 

I have 1 slicer based on d_entity and another based on d_entity_bis and I would like to substract the amount of entities selected with the first slicer by the amount of entities selected with the second slicer.

 

I tried this measure, but it did not work:

Diff = 
CALCULATE ( SUM ( Database[Amount] ); ALLSELECTED ( d_entity[entity_code] ) ) - 
CALCULATE ( SUM (  Database[Amount] ); ALLSELECTED ( d_entity_bis[entity_code] ) )

 

Any ideas ?

 

Here below the screenshot of my test case:

 

 

 

General Comment
Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    HI Anonymous ,

    In your scenario, we need to remove the relationship between these tables, then use the following DAx query:

    Diff =
CALCULATE (
    SUM ( Database[Amount] ),
    FILTER (
        ALL ( Database ),
        Database[Entity_code] IN ALLSELECTED ( d_entity[entity_code] )
    )
)
    - CALCULATE (
        SUM ( Database[Amount] ),
        FILTER (
            ALL ( Database ),
            Database[Entity_code] IN ALLSELECTED ( d_entity_bis[entity_code] )
        )
    )

    The result will like below: 

    By the way, the custom visual hierarchy slicer has some problem, when I select the A and B, it will display the true data, but when I select the Grp_1, it will not display the true data.

    Best Regards,

    Teige

2 Replies

    • TeigeGao's avatar
      TeigeGao
      Icon for Solution Sage rankSolution Sage
      7 years ago

      HI Anonymous ,

      In your scenario, we need to remove the relationship between these tables, then use the following DAx query:

      Diff =
CALCULATE (
    SUM ( Database[Amount] ),
    FILTER (
        ALL ( Database ),
        Database[Entity_code] IN ALLSELECTED ( d_entity[entity_code] )
    )
)
    - CALCULATE (
        SUM ( Database[Amount] ),
        FILTER (
            ALL ( Database ),
            Database[Entity_code] IN ALLSELECTED ( d_entity_bis[entity_code] )
        )
    )

      The result will like below: 

      By the way, the custom visual hierarchy slicer has some problem, when I select the A and B, it will display the true data, but when I select the Grp_1, it will not display the true data.

      Best Regards,

      Teige