Substract value based on 2 filters on the same dimension

[Anonymous]

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:

[Anonymous]

Need help !

Here is the PBIX if needed.

Best regards

Emilien

[TeigeGao · Accepted solution]

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