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 ...
nandukrishnavs
Community Champion
Community ChampionAnonymous
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 🙂
nandukrishnavsCommunity Champion
Community ChampionAnonymous
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 🙂