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 leve...
Need Help
Tips and Tricks
Tutorial Requests
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.