topic Calculate Optimal Pricing (Fixed and Variable Costs) in DAX Commands and Tips

Calculate Optimal Pricing (Fixed and Variable Costs)

Anonymous — Mon, 07 Sep 2020 17:38:50 GMT

Hi,

We have something like this with various pricing. The fixed price is including a certain amount of users and then the additional users are charged on top of that. Some examples:

Small Users Package (Including 50 Users) | Additional Price per user (Above 50)

$99 $1.98

Medium Users Package (Including 500 Users) | Additional Price per user (Above 500)

$799 $1.60

For data we have:

Company Name | User Amount | Pricing

Test Company 100 198

Now a company could be in a 'non-ideal' pricing deal and I'd like to calculate when it becomes cheaper to move to a Medium package for example. What calculation or dax logic would have to be used?

Thanks!

Re: Calculate Optimal Pricing (Fixed and Variable Costs)

Greg_Deckler — Mon, 07 Sep 2020 18:03:12 GMT

Anonymous - Here is one way to do it. See Table (33) of attached PBIX below sig.

Column = VAR __Table = GENERATESERIES([User Amount],500,1) VAR __Table1 = ADDCOLUMNS(__Table,"Price",[Pricing] + ([Value] - [User Amount])*1.98) VAR __UserCount = MINX(FILTER(__Table1,[Price]>=799),[Value]) RETURN __UserCount

It's basically an implementation of a psuedo-While loop.

Re: Calculate Optimal Pricing (Fixed and Variable Costs)

amitchandak — Tue, 08 Sep 2020 02:57:19 GMT

Anonymous , Static Way a new column

Switch( True(),
[User Amount] <0 50 ,99
[User Amount] < 500 , 99 + (1.98)*([User Amount]-50)
,799 + (1.60)*([User Amount]-500)
)