topic Re: Calculating Prices based on Fixed + Variable Costs in DAX Commands and Tips

Calculating Prices based on Fixed + Variable Costs

Anonymous — Mon, 17 Aug 2020 10:56:16 GMT

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

Re: Calculating Prices based on Fixed + Variable Costs

nandukrishnavs — Mon, 17 Aug 2020 10:15:11 GMT

Anonymous

Please provide a few rows and expected results

Re: Calculating Prices based on Fixed + Variable Costs

Anonymous — Mon, 17 Aug 2020 10:17:24 GMT

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)

Re: Calculating Prices based on Fixed + Variable Costs

Anonymous — Mon, 17 Aug 2020 10:48:03 GMT

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

Re: Calculating Prices based on Fixed + Variable Costs

Anonymous — Mon, 17 Aug 2020 10:57:38 GMT

nandukrishnavs I've added some rows. Hope this helps

Re: Calculating Prices based on Fixed + Variable Costs

Anonymous — Mon, 17 Aug 2020 10:58:11 GMT

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

Re: Calculating Prices based on Fixed + Variable Costs

Anonymous — Mon, 17 Aug 2020 11:20:21 GMT

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)

Re: Calculating Prices based on Fixed + Variable Costs

nandukrishnavs — Mon, 17 Aug 2020 11:30:48 GMT

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 🙂

Re: Calculating Prices based on Fixed + Variable Costs

nandukrishnavs — Mon, 17 Aug 2020 11:51:07 GMT

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 🙂