Dynamically Rank Items Based on Sales per Month

Question

Hello All,&nbsp;I'm Trying To generate the Rank Column in the following Table&nbsp;MonthColumn1CustomerSalesMonthly Customer SalesRank1-JanJanCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,714&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 73,90822-JanJanCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,793&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 73,90823-JanJanCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,401&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 73,90824-JanJanCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,445&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 78,97115-JanJanCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,388&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 78,97116-JanJanCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,138&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 78,97117-JanJanCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 73,43538-JanJanCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,776&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 73,43539-JanJanCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,642&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 73,43531-FebFebCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21,175&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 55,02332-FebFebCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,873&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 55,02333-FebFebCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,975&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 55,02334-FebFebCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,087&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 57,07625-FebFebCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,605&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 57,07626-FebFebCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,384&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 57,07627-FebFebCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,730&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71,85618-FebFebCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,634&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71,85619-FebFebCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,492&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71,85611-MarMarCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,603&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 62,71322-MarMarCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,636&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 62,71323-MarMarCustomer1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,474&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 62,71324-MarMarCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,958&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61,09135-MarMarCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,683&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61,09136-MarMarCustomer2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,450&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61,09137-MarMarCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,982&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 72,66118-MarMarCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,464&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 72,66119-MarMarCustomer3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,215&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 72,6611&nbsp;Rank here would be described as Rank of the Customer Total Sales per Given Month on a given month&nbsp;

jihwan_kim · Accepted Answer

&nbsp;New Table = SUMMARIZECOLUMNS (Dates[Month Name],Customers[Customer],"@monthly sales", SUM ( Sales[Sales] ),"@monthly sales rank",VAR _currentmonth =MAX ( Dates[Month Name] )VAR _ranktable =SUMMARIZE (CALCULATETABLE (ALL ( Sales ),Dates[Month Name] = _currentmonth),Customers[Customer])VAR _ranking =RANKX (_ranktable,CALCULATE (SUM ( Sales[Sales] )),,DESC)RETURN_ranking)&nbsp;&nbsp;https://www.dropbox.com/s/xrfoxswb50na430/mfav2.pbix?dl=0&nbsp;

Forum Discussion

Dynamically Rank Items Based on Sales per Month

1 Reply

Related content

Dynamic Calculation Group Item

dynamic x-axis based on filter

Dynamic images based on selections

Dynamic Filtering based on date selection

Dynamic Buckets based on slicer

Month	Column1	Customer	Sales	Monthly Customer Sales	Rank
1-Jan	Jan	Customer1	20,714	73,908	2
2-Jan	Jan	Customer1	27,793	73,908	2
3-Jan	Jan	Customer1	25,401	73,908	2
4-Jan	Jan	Customer2	25,445	78,971	1
5-Jan	Jan	Customer2	26,388	78,971	1
6-Jan	Jan	Customer2	27,138	78,971	1
7-Jan	Jan	Customer3	14,017	73,435	3
8-Jan	Jan	Customer3	29,776	73,435	3
9-Jan	Jan	Customer3	29,642	73,435	3
1-Feb	Feb	Customer1	21,175	55,023	3
2-Feb	Feb	Customer1	10,873	55,023	3
3-Feb	Feb	Customer1	22,975	55,023	3
4-Feb	Feb	Customer2	26,087	57,076	2
5-Feb	Feb	Customer2	20,605	57,076	2
6-Feb	Feb	Customer2	10,384	57,076	2
7-Feb	Feb	Customer3	29,730	71,856	1
8-Feb	Feb	Customer3	12,634	71,856	1
9-Feb	Feb	Customer3	29,492	71,856	1
1-Mar	Mar	Customer1	24,603	62,713	2
2-Mar	Mar	Customer1	10,636	62,713	2
3-Mar	Mar	Customer1	27,474	62,713	2
4-Mar	Mar	Customer2	24,958	61,091	3
5-Mar	Mar	Customer2	15,683	61,091	3
6-Mar	Mar	Customer2	20,450	61,091	3
7-Mar	Mar	Customer3	18,982	72,661	1
8-Mar	Mar	Customer3	24,464	72,661	1
9-Mar	Mar	Customer3	29,215	72,661	1