Help with pulling in formula using Swith function

[paguy215]

I have case where I have created buckets to indicatite where a rolling 6-month average of total sales fall in relation to a bonus.  So for example, if rolling average of sales are less than 5000, then they are 50%, if sales are between 5001 and 10000 then they get 60%, etc...so then that number is multiplied by the commission they received ... so again for example, if they got $500 commission and had 6500 in sales, then that $500 gets multiplied by 60%

That works fine, however there is an ask to an additional item where if a certain type of order is completed, they get 50% of of that final payment.  So again for example, if they have one order number that makes up that $100 of that $500 commission, then they get $60.  But the problem with showing this at the order number level is the SWITCH formula now automatially pulls everyone in at 50% since the Total Sales for one item are all below 5000 ... so basically the formula is pulling in the total Sales for just that one item instead of the total sales of all items for that person, and is showing as 50% instead of the 60% they should be getting.

Is there any way I can pull in the correct % bucket to show for all tickets for that one agent on each individual line?  Or do I need a whole new method

[Anonymous]

Hi @paguy215 ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

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

[dharmendars007]

Hello @paguy215 

Please create a rolling average measure first for each agent and then pass this measure to switch condition.

Rolling6MonthTotalSales =
CALCULATE(SUM(Sales[Amount]),
DATESINPERIOD(Sales[Date],MAX(Sales[Date]),-6,MONTH))

PercentageBucket =
SWITCH(TRUE(),
[Rolling6MonthTotalSales] <= 5000, 0.5,
[Rolling6MonthTotalSales] > 5000 && [Rolling6MonthTotalSales] <= 10000, 0.6,
[Rolling6MonthTotalSales] > 10000, 0.7,0)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[paguy215]

Also the 'exceptioncheck' in that SWITCH is to account for two agents that automatically have a fixed percentage

[paguy215]

Thanks...this is basically what I've been doing and what is causing this issue:

here are my formulas, is there something I am missing below?

6MRA = CALCULATE(

   SUM(  'Sales'[TotalSales] ),

    DATESINPERIOD( 'Calendar'[End of Mth], SELECTEDVALUE( 'Calendar'[End of Mth])-0 , -6,  MONTH )

)

TierBucket =

SWITCH ( TRUE (), SELECTEDVALUE ('Sales'[exceptioncheck1] ) = 1, .85, SELECTEDVALUE ( 'Sales'[exceptioncheck2] ) = 1, .65, [6MRA] >= 0 && [6MRA] < 5000, .50, [6MRA] >= 5000 && [6MRA < 10000, .60, [6MRA] >= 10000 && [6MRA] <20000, .70, [6MRA] >= 20000 && [6MRA] < 30000, .80, .90 )

[lbendlin]

Most likely you need to change the order of the checks in the SWITCH statement. Remember that SWITCH exits as soon as the condition evaluates to TRUE()

[paguy215]

Thanks...do you know what I would be switching?

SWITCH ( TRUE (), SELECTEDVALUE ('Sales'[exceptioncheck1] ) = 1, .85, SELECTEDVALUE ( 'Sales'[exceptioncheck2] ) = 1, .65, [6MRA] >= 0 && [6MRA] < 5000, .50, [6MRA] >= 5000 && [6MRA < 10000, .60, [6MRA] >= 10000 && [6MRA] <20000, .70, [6MRA] >= 20000 && [6MRA] < 30000, .80, .90 )

The exceptioncheck part of this formula is just because 2 agents have an automatic rate regardless of the actual sales number, i don't believe that would impact anything