Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago
Solved

Find Personal Best

Hello - How can I find which month was the best month and sales in that month for each agent?   Please click here to download the PBIX file. Click Here   Thanks   Result is to be shown as below...
Need Help
Show and Tell
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi gauravnarchal 

    Try this,

    "How can I find which month was the best month", create the measures below, it returns the best month according to agent,

    sumsales = CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),'Table'[Agent]=MIN('Table'[Agent]) && 'Table'[MonthYear]=MIN('Table'[MonthYear])))
    bestmonth = 
VAR _maxsales =
    MAXX ( ALL ( 'Table' ), [sumsales] )
RETURN
    CALCULATE (
        MAX ( 'Table'[MonthYear] ),
        FILTER ( ALL ( 'Table' ), 'Table'[sumsales] = _maxsales )
    )

    "sales in that month for each agent", create the table below,

    sales_each_agent = FILTER('Table','Table'[MonthYear]='Table'[bestmonth])

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

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

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago

Hi amitchandak  Thank you for helping me.

 

I have tried to use this measure, but I am getting the rank 1 as result for all agents/months.

 

rank = rankx(filter(summarize(ALLSELECTED(Invoices), Invoices[SaleAgent], 'Calendar Ultimate'[MonthYear]), Invoices[SaleAgent] = max(Invoices[SaleAgent])), [Total Sales],,desc,dense)