SUMX(ADDCOLUMNS(VALUES()))) Incorrect Total

DAX DAX Calculations
1

Hierarchy:Region>Sub Region>Facility>Address>Location

I’m trying to calculate total appts by address. For levels higher than Address, it will sum the appt at each address under that level. For levels lower than Address(For example, location), it will ignore that level and calculate at address level.

DAX:
Testing Appt by Address=
SUMX (
ADDCOLUMNS (
VALUES(‘Rooms’[Rooms Address])
,
“A”,

                  CALCULATE([Number of Appointments] ,ALLSELECTED('Rooms')
                
                )
                   
        ),[A]

)

Confusion: I don’t understand why it returned correct numbers at address and location levels ,but incorrect at levels higher than address(It looks like it did Number of Address * Total Appt at that Level, for example at Georgetown, 2*279). It 's like VALUES() didn’t filter.

I also tried:

  1. wrapping it in another calculate and didn’t work:
    CALCUALTE(CALCULATE([Number of Appointments] ,ALLSELECTED(‘Rooms’))
  2. Adding values() within CALCULATE and didn’t work:
    CALCULATE([Number of Appointments] ,ALLSELECTED(‘Rooms’), VALUES(‘Rooms’[Rooms Address]))
    3.Wrapping 2 within calculate, it worked:
    CALCULATE(CALCULATE([Number of Appointments] ,ALLSELECTED(‘Rooms’),VALUES(‘Rooms’[Rooms Address])))

Can someone please explain to me why Testing Appt by Address,Testing Appt by Address Tyr 1, and Testing Appt by Address Tyr2 didnt work?

Thank you.

Capture
Capture937×475 23.3 KB

2

IMG_8127
IMG_81272732×2048 556 KB

Check out this answer here. You can continue a thread from this base query

3

This measure provided with All will remove all filters , I am not sure if it will give consistent results given the problem , @Zzy Can you pls validate the measure provided and let us know if it work if not kindly share the pbix file for us to debug

4

@Zzy Here are the likely cause , pls corelate if it make sense

your first measure

Testing Appt by Address is not working because context transition is not happening , allselected(rooms) is cancelling out the context transition for the row context created values .
this produce right data at address and row location because values there is just one address at that level which is being iterated
When the calculation moves to facility level ,the values will retrun 2 address and for each address counting of room happens hence its giving 2 time result.

Why try3 works ? I think this is likely reason
However , when you use 2 calculate , the inner calculate is protecting removal of row context due to allselected(rooms) .
Thats why likely this measure work