topic Re: Calculating sales orders, while ignoring certain id's in DAX Commands and Tips

Calculating sales orders, while ignoring certain id's

FrederikAhring — Sat, 16 Apr 2022 12:30:49 GMT

Hi PowerBI Community.

I have a little complex sales-orders and needs some help designing the measure.
I want to create a measure that sums the user price for the order.

If the order contains only "User" - summarize.
If the order contains "Unlimited" i only want that value, and ignore the rest
If the order contains anything else (fx. Company) i want to ignore that one and just summarize the Users.

Order	Item	Price
1-ord	User	300
1-ord	User	300
2-ord	Unlimited	5000
2-ord	User	300
2-ord	User	300
3-ord	User	300
4-ord	User	300
5-ord	Company	10000
5-ord	User	300
5-ord	User	300

Expected outcome
Order 1: 600
Order 2: 5000
Order 3: 300
Order 4: 300
Order 5: 600

Unsure if this is possible, or if i should maybe add a conditional column so i have only Users and unlimited in a new column, as there are many different variations than "Company" which i want to ignore.

Really hope you can help

Re: Calculating sales orders, while ignoring certain id's

daXtreme — Sun, 17 Apr 2022 18:06:00 GMT

Something like this:

[Your measure] = sumx( distinct( T[Order] ), calculate( switch( true(), // if all visible items are User -> SUM the price and( distinctcount( T[Item] ) = 1, "user" in distinct( T[Item] ) ), sum( T[Price] ), // if one of the items is Unlimited -> get the value for Unlimited // what happens if there are 2 Unlimited's in the same order? "unlimited" in distinct(T[Item] ), calculate( selectedvalue( T[Price] ), T[Item] = "unlimited" ), // if order contains anything else -> SUM the price for User calculate( sum( T[price] ), keepfilters( T[Item] = "user" ) ) ) ) )

Re: Calculating sales orders, while ignoring certain id's

FrederikAhring — Sat, 16 Apr 2022 13:08:43 GMT

Wow, thats a bit more complicated than i would have figured out.
Thanks a lot, I will give it a try.

Luckily, there can't be two unlimited in same order.

Re: Calculating sales orders, while ignoring certain id's

FrederikAhring — Sun, 17 Apr 2022 14:32:38 GMT

Very glad to inform you that it works.
Thank you so much, the help is greatly appreciated.

BR Frederik