cancel
Showing results for
Did you mean:
Frequent Visitor

Dynamically Rank Items Based on Sales per Month

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

1 ACCEPTED SOLUTION
Super User

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
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors