ALLEXCEPT not working

[hamishpolson]

I am trying to remove row context of specific columns. I am trying to return a % of total sales relevent to a specific sales channel for each product. In otherwords every product when viewed across each channel would total 100%. To do this I am creating a VAR totalSales which should calculate total sales for each product across total market. However my current result is giving me a % of total sales for all products and all markets.

I would really appreciate anyone who can try and help point me in the right direction as I have not been able to solve this for the last few days!

	Channel A	Channel B
Brand A	65%	35%
Product A	50%	50%
Product B	40%	60%
Product C	70%	30%

Dol SOT CY = 
VAR totalSales = 
    calculate(
        SUM(Data[Dollars]),
        ALLEXCEPT(Data,Data[Product])
        )
           
Var sales = SUM(Data[Dollars])
   
 Return
    sales/totalSales

[Anonymous]

By the way... ALLEXCEPT is a very dangerous function. To know when to use it you have to study it in great depth.

Best
D

[Anonymous · Accepted solution]

Probably something like this:

[Total] = SUM( Data[Dollars] )

[Dol SOT CY] = 
VAR __totalAcrossMarkets = 
    CALCULATE(
        [Total],
		VALUES( Data[Product] ),
		ALL( Data )
	)    
VAR __currentTotal = [Total]
VAR __result =
	DIVIDE( __total, __totalAcrossMarkets )
RETURN
	__result

Best

D