Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX Measure Ignoring one specific

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.

ZoneCountryDM Sold HoursWanted Ouput
LATArgentina90100
LATColombia10100
ASJapan2050
ASChina3050
Need Help
Tutorial Requests
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    Hi, Anonymous 
    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

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    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.

4 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    1 year ago

    Hi, Anonymous 
    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous ,

         

        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.