DAX Measure Ignoring one specific

[RubenSO]

Hey community!
I've been struggling with a measure that in theory shoulnd't be a problem. 
I have a measure called "DM Sold Hours" that I use for a table with data by Area and I want another one that use it but ignoring the subsidiary level.
For example this sample of the data, I want all subsidiaries from LAT to be equal to 100 (90+10)  and AS to be 50 (20+30)
I tried with ALL, SELECTEDVALUE, and FILTER functions inside CALCULATE but not sure it doesn't work.

Zone	Country	DM Sold Hours	Wanted Ouput
LAT	Argentina	90	100
LAT	Colombia	10	100
AS	Japan	20	50
AS	China	30	50

[vojtechsima · Accepted solution]

Hi, @RubenSO 
try this measure:

New Output = 
var currrentZone = SELECTEDVALUE('Table'[Zone])
var _sum = SUMX(FILTER(ALL('Table'), 'Table'[Zone] = currrentZone), 'Table'[DM Sold Hours])

return _sum

[vojtechsima · Accepted solution]

Hi, @RubenSO 
try this measure:

New Output = 
var currrentZone = SELECTEDVALUE('Table'[Zone])
var _sum = SUMX(FILTER(ALL('Table'), 'Table'[Zone] = currrentZone), 'Table'[DM Sold Hours])

return _sum

[RubenSO]

Thanks for this formula!
Seems I'm facing another issue here, because my report has another filters by report level to excluded no current rows  like [customer actives], [Agent Name], [customer excluded] are the main ones. I tried to use ALLEXCEPT but doesn't seem to work because the ouput it's like a bigger number that the expected one.

[v-rzhou-msft · Accepted solution]

Hi @RubenSO ,

According to your statement, I think your issue is that you want to keep filter in your measure. 

I think you can try ALLSELECTED() FUNCTION.

New Output =
VAR currrentZone =
    MAX ( 'Table'[Zone] )
VAR _sum =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Zone] = currrentZone ),
        'Table'[DM Sold Hours]
    )
RETURN
    _sum

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RubenSO]

Thank you!
The value was a little bit different but adjusted with CALCULATE and using ALLSELECTED did the trick!