Forum Discussion

FrederikAhring's avatar
FrederikAhring
Frequent Visitor
4 years ago
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 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.

OrderItemPrice
1-ordUser300
1-ordUser300
2-ordUnlimited5000
2-ordUser300
2-ordUser300
3-ordUser300
4-ordUser300
5-ordCompany10000
5-ordUser300
5-ordUser300


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

Need Help
  • daXtreme's avatar
    daXtreme
    4 years ago

    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" )
            )
        )
    )
)

     

3 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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" )
            )
        )
    )
)

     

    • FrederikAhring's avatar
      FrederikAhring
      Frequent Visitor
      4 years ago

      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.

  • FrederikAhring's avatar
    FrederikAhring
    Frequent Visitor
    4 years ago

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

    BR Frederik