calculation based on multiple conditions and parameter value

[Evandam]

Hi there,

i was wondering if its possible to calculate (either via measure or calculated column) a value based on multiple conditions AND including a parameter value.

Scenario:

for each company a base fee is charged on the number of users. The fee per user depends on the company type (banking, construction, health care) and is set in a parameter.

next to this there is a fee for exceending the number of access points and/or speed.
for each company type the treshold is defined:
banking: 4 access points | 15 speed
construction: 5 access points | 10 speed
health care: 3 access points | 5 speed

if the actual number (of speed and/or access point) exceed the treshold the following is to be charged 

per access point €500 extra
per 1 speed €250 extra

example

company BAM is a construction company with 625 users, 15 speed, 9 access points.

if the user fee parameter for construction is set to €5,25 the following should be charged:
(€5,25*625) + ((9-5)*500) + ((15-10)*250) = €6.531,25

is the possible at all?

Link to PBIX with dummy data: 

[v-jingzhang · Accepted solution]

Hi @Evandam

For each company's charge, you can use the following measure.

Charge = 
SWITCH(SELECTEDVALUE(Companies[Company type]),
"Banking",[Parameter_Fee_Banking Value]*SUM(Companies[Users])+MAX(SUM(Companies[Access points])-4,0)*500+MAX(SUM(Companies[Speed])-15,0)*250,
"Construction",[Parameter_Fee_Construction Value]*SUM(Companies[Users])+MAX(SUM(Companies[Access points])-5,0)*500+MAX(SUM(Companies[Speed])-10,0)*250,
"Health care",[Parameter_Fee_HealtCare Value]*SUM(Companies[Users])+MAX(SUM(Companies[Access points])-3,0)*500+MAX(SUM(Companies[Speed])-5,0)*250
)

However above measure doesn't calculate the total charge of multiple companies correctly if you want to show the totals. So you need to use the following measure which calculates the sum of above measure. 

Total Charge = SUMX(VALUES(Companies[Company name]),[Charge])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.