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 o...
  • daXtreme's avatar
    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" )
                )
            )
        )
    )