Find the Top 01 in Sales Group with Same Customer with last 03 month

[ADSL]

Hi BI Community Team,

I have a sales order table for last 03 month that 01 customer service by multiple sales rep with different group.

Any suggestion/advise how to find which group are the Top 01 for last 03 month of these customer?

Expect Result:

Thanks and Regards,

[v-zhangti]

Hi, @ADSL 

You can try the following methods.
Measure:

Sum = CALCULATE(SUM(SalesOrder[Amount]),ALLEXCEPT(SalesOrder,SalesOrder[Group Name]))

Measure = 
Var _table=SUMMARIZE(ALL(SalesOrder),SalesOrder[Group Name],"SUM",[Sum])
Var _MaxSum=MAXX(_table,[SUM])
Return
IF([Sum]=_MaxSum,1,0)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[ADSL]

Hi @v-zhangti ,

After I have add a few more customers and apply these measure, the result is not correctly.

You can check the screenshot below. Group D is the top that sort by group, but when applied the measure that you feedback. Then group D is always the Top.

As per expectation, we need to find the Top 01 of Sales Rep that assigne to which group.

If we look at the yellow highlight, it's correct one.

Any suggestion?

Thanks and Regards,

[v-zhangti · Accepted solution]

Hi, @ADSL 

You can try the following methods.

Measure = 
Var _MaxValue= CALCULATE( MAX(SalesOrder[Amount]), ALLEXCEPT(SalesOrder, SalesOrder[Customer Code]))
Var _Sum=SUM(SalesOrder[Amount])
Return
IF(_MaxValue=_Sum,1,0)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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