DAX Formula to Sum by a "hidden" group / field

Question

I have a table with the following data:

Customer	Category	Product	Color	Quantity
A	Alpha	CC	Blue	200
A	Alpha	RI	Red	100
B	Alpha	CC	Blue	100
B	Alpha	FA	Blue	50
B	Alpha	RI	Red	100
C	Alpha	EL	Blue	200

I would like to summarize the data in an excel pivot, by all the columns except the 'customer', like this:

Category	Product	Color	Sum of Quantity	Quantity_Customer
Alpha	CC	Blue	300	550
Alpha	EL	Blue	200	200
Alpha	FA	Blue	50	250
Alpha	RI	Red	200	550

The measure 'Quantity_Customer' should show the total quantity of all 'customers' which have the appropriate category/product/color.

For example: The value of the measure 'Quantity_Customer' in the first line should be 550, since customer A (with a total quantity of 300) and customer B (with a total quantity of 250) both have the Article Alpha/CC/Blue (category/product/color).

I tried the following DAX: =CALCULATE(SUM(Table5[Quantity]);ALLEXCEPT(Table5;Table5[Customer]))

But this only works, when the customer is displayed as column in the table (pivot). Otherwise the value of 'Quantity_Customer' is always displayed as 750.

Is there a way, to sum the quantity by customer, even if this field is "hidden"?

Answer 1

I calculated the sum of the quantity for each customer identified by the outer SUMX function and keeping the context of all other columns except Customer. The ALLEXCEPT will remove the filters from all columns except the specified ones keeping the Customer context intact. T

   Quantity_Customer = 
    SUMX(
        VALUES(Table5[Customer]), 
        CALCULATE(
            SUM(Table5[Quantity]), 
            ALLEXCEPT(Table5, Table5[Customer])
        )
    )