Dynamically Rank Items Based on Sales per Month

[mfa]

Hello All,

I'm Trying To generate the Rank Column in the following Table

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

Rank here would be described as Rank of the Customer Total Sales per Given Month on a given month

[Jihwan_Kim · Accepted solution]

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

)

https://www.dropbox.com/s/xrfoxswb50na430/mfav2.pbix?dl=0