cancel
Showing results for
Did you mean:
Helper II

## calculation based on multiple conditions and parameter value

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?

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors