Ranking Groups by Totals per Day Based on Date Selection

[AccrualJoke]

Hello, i'm having trouble presenting the following in a visual: Based on the date selected, display customers from highest to lowest based on sales for that date. However, customers below top N should be grouped and displayed as "Others".

I've attempted to create a calculated column via RANKX to rank each of the customers, but I keep getting circular reference errors or the ranks show up as 1 across all cells. I can't seem to get the formula to properly rank each customer within each date based on their sales for each date. From there, I'd want to create a conditional column that'll show the customer's name if it appears in the top 3, for example, and anything below would be displayed as "Others".

Attached is an example I manually created. As you can see, I'm trying to create a Rank and Display Name column based on the date, customer name, and sales. It should only rank within each date based on the customer's total sales for that date.

Thanks in advance to anyone who can figure out how to solve this.

[Ruchi_123]

Good day, everyone

I'm looking for the time that has the most orders in a day.
On a daily basis, I'd like to highlight maximum order.

Please find the picture below for the refence

[v-yuaj-msft · Accepted solution]

Hi @AccrualJoke ,

Based on your description, you can create some measures and columns as follows.

measures:

x1 = SUM(Sales[Sales])

x2 = RANKX(ALLSELECTED(Sales[Customer]),[x1],,DESC)

Calculated columns:

calculated_rank = CALCULATE([x2],ALLEXCEPT(Sales,Sales[Customer],Sales[Date]))

calculated_Displayed Name = IF([calculated_rank]<=3,[Customer],"Other")

Result:

Total sales using slicer

_TOTAL = SUMX(FILTER(ALL(Sales),'Sales'[Date]=SELECTEDVALUE(Sales[Date])&&'Sales'[Displayed Name]=SELECTEDVALUE(Sales[Displayed Name])),'Sales'[Sales])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[amitchandak]

@AccrualJoke ,

Sales Total =
var _max =maxx(allselcted('Date', 'Date'[Date])
return
calculate(sum(Table[Sales]), filter(Table, Table[Date] =_max))

Rank = rankx(allselected(Table[Customer]), [Sales Total],,desc, dense)

or

Rank = rankx(filter(allselected(Table[date],Table[Customer]),Table[date] =max(Table[date])), [Sales Total],,desc, dense)

For TopN with others refer : https://www.youtube.com/watch?v=UAnylK9bm1I

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[AccrualJoke]

@amitchandak 
Thank you for responding!
For some reason, I still can't get the rankings to work. I either end up with a circular reference or all ranks end up as 1. I just can't seem to make the table rank within each date based on sales grouped by customers...

Just to confirm:

• Total Sales = measure and Rank = calculated column?
• 'Date' = disconnected date table?
• For Total Sales, am I trying to force the sales to = total sales only for the selected date? If so, shouldn't I be using SELECTEDVALUE(Date[Date]) instead of MAXX(ALLSELECTED(Date[Date])? Either way, when I try either method, it still doesn't result in proper rankings showing up.

[EDIT]: And to clarify, I'll need the Rank and display in the form of calculated columns. It seems that ranking via measure works in isolation. But since I need the rankings to determine how the customer names are displayed in visuals, such as a legend in a pie chart, I cannot use measures.