Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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?

 

Link to PBIX with dummy data: 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous

     

    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.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous

     

    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.