Forum Discussion

E_'s avatar
E_
Frequent Visitor
2 years ago

Calculating total useing ALLEXCEPT

I'm calculating the totals using ALLEXCEPT. What I want is: given an ID (that can be on more than one segment) I want its segment industry.

 

It only works on the second level, the first level it's calculating the sum of the month (161,615) like the following and not 17,186 + 16,889 = 34,075 that is the industry of ID .

 

 

Obs.: ID and Segment is on the same table.

 

My measure is:

 

 

 

CALCULATE(
    SUM(Consolidated[quantity]),
    ALLEXCEPT(
        Consolidated,
        Consolidated[channel1],
        Consolidated[channel2],
        Consolidated[segment],
        Consolidated[division],
        Consolidated[region],
        Consolidated[district]
    ),
    TREATAS(VALUES('Calendar'[Date]), Consolidated[registration_date]),
    Consolidated[company] <> BLANK()
)

 

 

 

 

 

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, E_ 

    Based on your information, I create a sample table:

    Then create a new measure and try the following DAX expression:

    Measure = CALCULATE(
    SUM('Table'[quantity]),
    ALLEXCEPT(
        'Table',
        'Table'[ID],  -- Ensure ID is included in the ALLEXCEPT function
        'Table'[channel1],
        'Table'[channel2],
        'Table'[segment],
        'Table'[division],
        'Table'[region],
        'Table'[district]
    ),
    TREATAS(VALUES('Calendar'[Date]), 'Table'[registration_date]),
    'Table'[company] <> BLANK()
)

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

    • E_'s avatar
      E_
      Frequent Visitor
      2 years ago

      It calculates a normal SUM

       

      What I want to do is: given the ID1, give me back the industry of Segment 1 + industry of Segment 2 (17,186 + 16,889) when we are looking at the first level and not the month total.