Forum Discussion
Calculating Prices based on Fixed + Variable Costs
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
8 Replies
- nandukrishnavs
Community Champion
Anonymous
Please provide a few rows and expected results
nandukrishnavs I've added some rows. Hope this helps
- 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 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)- 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 🙂
