Calculating Prices based on Fixed + Variable Costs

[Anonymous]

Hi, I could really use some help with the following calculation. let's say we have different categories with both fixed and variable pricing for our customers, based on the package they're in and the number of users our customers have.

For a 'small customer' the price would be 25USD for up to 10 users, + 2 for every additional (over 10) user.
For a 'medium customer'the price would be 50USD for up to 25 users, + 1,50 for every additional (over 25 users)

We'd have these columns:

Customer, CustomerType, UserAmount, Price (expected results)

Customer1, Small Customer, 8,  25 (Just the package price (small))

Customer2, Small Customer, 12, 29 (package price (25) + 4 (2 additional users)

Customer 3, Medium Customer, 6, 50 (Package price (medium)

Customer 4, Medium Customer, 35, 65 (Package Price medium + 10 additional users (+1,50)

So the customers pay for the package they choose and will have to pay an extra amount if they end up having more users than currently are in the platform. 

How do I calculate this Price column? If possible I'd also like the calculation to include a filter based on CustomerType, because customers could be in customer types, regardless of the amount of users.

Thanks in advance,

Justin

[nandukrishnavs]

@Anonymous 

Try something like this

Price =
VAR __CustomerType = 'MyTable'[ CustomerType]
VAR __UserAmount = 'MyTable'[ UserAmount]
VAR __SmallCustomer =
    IF (
        __CustomerType = "Small Customer",
        IF ( __UserAmount <= 10, 25, ( ( __UserAmount - 10 ) * 2 ) + 25 )
    )
VAR __MediumCustomer =
    IF (
        __CustomerType = "Medium Customer",
        IF ( __UserAmount <= 25, 50, ( ( __UserAmount - 25 ) * 1.5 ) + 50 )
    )
VAR __finalresult =
    SWITCH (
        __CustomerType,
        "Small Customer", __SmallCustomer,
        "Medium Customer", __MediumCustomer
    )
RETURN
    __finalresult

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[nandukrishnavs]

@Anonymous 

Another option using DATATABLE.

Price = 
VAR __table =
    DATATABLE (
        "Type", STRING,
        "Limit", INTEGER,
        "Amount", CURRENCY,
        "Extra", DOUBLE,
        {
            { "Small Customer", 10, 25, 2 },
            { "Medium Customer", 25, 50, 1.5 }
        }
    )
VAR __CustomerType = 'MyTable'[ CustomerType]
VAR __UserAmount = 'MyTable'[ UserAmount]
VAR __Limit =
    MAXX ( FILTER ( __table, [Type] = __CustomerType ), [Limit] )
VAR __Amount =
    MAXX ( FILTER ( __table, [Type] = __CustomerType ), [Amount] )
VAR __Extra =
    MAXX ( FILTER ( __table, [Type] = __CustomerType ), [Extra] )
VAR __Result =
    IF (
        __UserAmount <= __Limit,
        __Amount,
        __Amount + ( ( __UserAmount - __Limit ) * __Extra )
    )
RETURN
    __Result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[harshnathani]

Hi @Anonymous ,

Create a Calculated Column

Column = 

SWITCH(
    TRUE(),
    'Table (2)'[Customer Type] = "Small Customer" && 'Table (2)'[User Amount] <=10 , 25,
    'Table (2)'[Customer Type] = "Small Customer" && 'Table (2)'[User Amount] >10 , 25 + (('Table (2)'[User Amount] - 10) * 2),
    'Table (2)'[Customer Type] = "Medium Customer" && 'Table (2)'[User Amount] <=25 , 50,
    'Table (2)'[Customer Type] = "Medium Customer" && 'Table (2)'[User Amount] > 25 , 50 + (('Table (2)'[User Amount] - 25) * 1.5)
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Here's how to ask questions to maximize the chances of an answer:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[harshnathani]

Hi @Anonymous ,

Please share some sample data in text format and the expected output.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani I've added some rows with example output. Hope this helps

[nandukrishnavs]

@Anonymous 

Please provide a few rows and expected results

[Anonymous]

@nandukrishnavs I've added some rows. Hope this helps