topic Re: ALLEXCEPT not working in DAX Commands and Tips

ALLEXCEPT not working

hamishpolson — Mon, 25 May 2020 22:30:03 GMT

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

Re: ALLEXCEPT not working

Anonymous — Tue, 26 May 2020 01:16:28 GMT

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

Re: ALLEXCEPT not working

Anonymous — Tue, 26 May 2020 01:17:13 GMT

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