Sales sum divide in bucket

[rrathod]

Hello Team,

i've monthly sales column and i am trying to calculate some kind of fees from that. Below are some highlights to calculate the same.

so if sum of monthly sales are 50,000 then it should group it into 3 category = first is 20,000 then again 20,000 and then 10,000
so we need to calculate 10% of first 20k and then 8% of second 20k and then 7% of another 20k likewise calclation will happen.

So at the end i want to calculate fees from 50k but as per above category.

Let me know if anyone can help me.

[v-yetao1-msft]

Hi @rrathod 

Did the solution provided by SU solve your problem? If it has been solved, please mark his method so that other people with the same problem can see it .

Best Regards

Community Support Team _ Ailsa Tao

[ryan_mayu]

@rrathod 

what's your percentage groups? 10%,8%,7%,6%, what are the rests? What if the monthly sales amount is very huge?

e.g 400,000 which can be separated to twenty 20,000?

[rrathod]

@ryan_mayu Actually we are going to group the monthly sales some based on products and we are sure that product sales sum for monmth will not cross 100,000. Maximum lit is 100K

• 0 - 20k: 15%
• 20 - 40k: 14%
• 40-60k: 13%
• 60-80k: 12%
• 80-100k: 11%

[ryan_mayu · Accepted solution]

@rrathod

is this what you want?

Measure = 
var a=int([monthly amount]/20000)
return if(a=0,[monthly amount]*0.15,if(a=1,20000*15*+([monthly amount]-20000*a)*0.14,if(a=2,20000*0.15+20000*0.14+([monthly amount]-20000*a)*0.13,if(a=3,20000*0.15+20000*0.14+20000*0.13+([monthly amount]-20000*a)*0.12,20000*0.15+20000*0.14+20000*0.13+20000*0.12+([monthly amount]-20000*a)*0.11))))

pls see the attachment below

[v-yetao1-msft]

Hi @rrathod 

What is your calculation rule for dividing sum of monthly sales ? As you said , if the sum of monthly sales are 50000,then divide it into 20000,20000,10000 .But if the sum of monthly sales are 60000 , how to divide it ? Hope you can provide a calculation rule for the sum of monthly sales .

Or you can create a measure like this :

fees =

var value1=20000*0.1

var value2=20000*0.08

var value3=(SELECTEDVALUE('Table'[Column1])-40000)*0.07

return

value1+value2+value3

And the effect is as shown :

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rrathod]

@v-yetao1-msft Thanks for the help! and whatever the sales amount for month is it should divide into 20,000 grouping so if sales amount 60,000 then (20,000*10% + 20,000*8% + 20,000*7) and if monthly sales is 85,000 then (20,000*10% + 20,000*8% + 20,000*7 + 20,000*6% + 5,000*5%).

This is how grouping should happen. we are eagerly looking for your response.

Once again thanks for the support!

[rrathod]

@Fowmy 

[rrathod]

@amitchandak