Forum Discussion
Post PatronALL, ALLEXCEPT how does it work?
I am trying to calculate total volume, by using ALL or ALLEXCEPT DAX, but cannot seem to get it working.
I have a simple dataset where both sales and volume data are mixed together (Line Item) and separated by forecast scenarios (Phase).
From here I want to calculate total volume for each forecast (phase)
However, none of the formulas is working. How can I get it to work?
Thanks for the reply.
I was sorting L3 brand by order, so I had to include this sort column under ALL function.
Then it worked perfect.
2 Replies
- rajendraongole1
Super User
Hi hidenseek9 - To get total volume per Phase, keeping only YearMonthnumber, and ignoring the rest (like entity level or L3 Order).Use ALLEXCEPT on the main fact table ('RF Data'), not dimension tables like 'L3 Order', unless you know exactly how the relationship affects context.
try the below logic:
Volume TTL RF3 =
CALCULATE(
SUM('RF Data'[Value]),
'RF Data'[Phase] = "RF3",
'RF Data'[Line Item] = "Volume",
ALLEXCEPT('RF Data', 'RF Data'[YearMonthnumber])
)Hope this helps.
hidenseek9Thanks for the reply.
I was sorting L3 brand by order, so I had to include this sort column under ALL function.
Then it worked perfect.
