Forum Discussion
Solved
Calculating sales orders, while ignoring certain id's
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 o...
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" ) ) ) ) )
daXtreme
Solution Sage
Solution SageSomething 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" )
)
)
)
)
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.