Rank Quarterly Sales Across Multiple Years

[DavidWest]

I have a transaction table with entries for sales events going back many years.

I can create a table showing total sales by year and each year's ranking (SalesRank = rankx(all(Transactions[Date].[year]),[SalesTotal],,DESC,Dense)) which functions as expected, showing total sales by year with each year ranked 1, 2, 3, etc.

If I add quarter from the date heirarchy to this table the ranking changes to the rank of each Q1 compared to the other Q1s, and each Q2 compared to the other Q2s.  I want it to show the rank of a specific quarter to ALL other quarters (so if I had 4 years of data, it would rank the quarters 1-16).  

This is what I get:

Year	Quarter	Sales	Rank
2022	2	$          455,518	1
2023	3	$          403,766	1
2023	1	$          318,794	1
2022	4	$          217,555	1
2022	1	$          300,701	2
2023	2	$          268,292	2
2022	3	$          261,584	2
2021	4	$          180,779	2
2021	3	$          234,085	3
2020	4	$          168,493	3
2021	2	$          161,936	3
2021	1	$          143,469	3
2020	2	$          127,698	4
2020	1	$          111,555	4
2020	3	$          102,345	4

This is what I want:

Year	Quarter	Sales	Rank
2022	2	$  455,518	1
2023	3	$  403,766	2
2023	1	$  318,794	3
2022	1	$  300,701	4
2023	2	$  268,292	5
2022	3	$  261,584	6
2021	3	$  234,085	7
2022	4	$  217,555	8
2021	4	$  180,779	9
2020	4	$  168,493	10
2021	2	$  161,936	11
2021	1	$  143,469	12
2020	2	$  127,698	13
2020	1	$  111,555	14
2020	3	$  102,345	15

I tried using a different measure for changing year to quarter (SalesRankQ = rankx(all(Transactions[Date].[Quarter]),[SalesTotal],,DESC) but every quarter comes back with a ranking of "1."

Note - I don't have a Date table.  Since the data heirarchy is already there I haven't had a need to create one (and when I did and linked it to my transaction table it messed up most of my existing reports).

Any help is appreciated!

. 

[FreemanZ · Accepted solution]

hi @DavidWest ,

not sure if i fully get you, supposing you have data table like:

Date	Sales
1/1/2022	1
4/1/2022	2
7/1/2022	3
10/1/2022	4
1/1/2023	9
4/1/2023	8
7/1/2023	7
10/1/2023	6

try to

1) add a calculated column like:

qtr = FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" )

2) plot a table visual with qtr column, sales column and a measure like:

rank = 
RANKX(
    ALLSELECTED(data[qtr]),
    CALCULATE(SUM(data[sales]))
)

it worked like:

[FreemanZ · Accepted solution]

hi @DavidWest ,

not sure if i fully get you, supposing you have data table like:

Date	Sales
1/1/2022	1
4/1/2022	2
7/1/2022	3
10/1/2022	4
1/1/2023	9
4/1/2023	8
7/1/2023	7
10/1/2023	6

try to

1) add a calculated column like:

qtr = FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" )

2) plot a table visual with qtr column, sales column and a measure like:

rank = 
RANKX(
    ALLSELECTED(data[qtr]),
    CALCULATE(SUM(data[sales]))
)

it worked like:

[DavidWest · Accepted solution]

Thanks!

I did: Qtr = FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" )

Your ranking didn't work for me, but using your Qtr calculation I got this to work: 

SalesRankQ = rankx(all(Transactions[Qtr]),[SalesTotal],,DESC)

I really appreciate your help (and the sample file you provided was invaluable).