cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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:

1 ACCEPTED SOLUTION
Solution Sage

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 2
Anonymous
Not applicable

Need help !

Here is the PBIX if needed.

Best regards

Emilien

Solution Sage

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.