Forum Discussion
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
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.
4 Replies
gauravnarchal , Create a measure like this on sales measure and use visual level filter rank =1
rank = rankx(filter(summarize(allselecetd(Table), Table[Agent], Table[Month Year]), [Agent] = max([Agent])), [sales],,desc,dense)
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)gauravnarchal , In visual , you are only using these two Invoices[SaleAgent], 'Calendar Ultimate'[MonthYear] . No other column?
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
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.
