Rank X by Sales Total by Month Year and Country

[Bopps]

Hi there,

I need to rank my terminals by sales (I have manged to get an overall rank by ALL terminals across all months)

I need to rank each country each month indivudually. 

I created a table with sales totals  by month year by terminal by country 

Then created a rankx but this ranks everything

How do i change the ranking to rank each terminal by month year and by country. 

So i want each country to have the rank for each month

I hope this makes sense. 

Thank you 

[Bopps]

Thank you so much, this seem to be working. 

I need to figure out how to only show the top ten now 🙂

[Jihwan_Kim]

Hi, @Bopps 

I tried to create a similar sample pbix file based on the explanation.

Please try the below to create a new column.

Rank Test CC =

VAR currentperiod = 'Sales Rank Table'[Month Year]

RETURN

RANKX (

FILTER (

ALL ( 'Sales Rank Table' ),

'Sales Rank Table'[Month Year] = currentperiod

),

'Sales Rank Table'[Sales Total],

,

DESC

)

https://www.dropbox.com/s/xh99zrdiiq9ocdv/bopps.pbix?dl=0 

Hi, My name is Jihwan Kim.

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

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Bopps]

@Jihwan_Kim 

Hi there,

i tried you solution, it does seem to do something but not 100% as yet. 

I have filteres out the top rank (top 1) in the table, 

I would expect to see one for each period (back to April 2020) and one for each operation, 12 in total 

Below is the screenshot, really not sure why it isnt working? This is the full list returned by filtering "1"

[Jihwan_Kim]

Hi, @Bopps 

Thank you for your feedback.

Sorry that I quite do not understand your question.

You have data for Jan/Feb/Mar 2020 but you do not want to see those?

Please share your sample pbix file's link here, with how you want to see your desired outcome.

Then I can try to look into it to come up with a more accurate solution.

Thank you.

[Bopps]

@Jihwan_Kim 

https://wetransfer.com/downloads/9f26e02124ced923c21602f228a6471a20210518114403/68d284f7b1cdc1407860...

Fuile linked above. 

I need to rank the sales for each month for each country

Hope this helps 

[Jihwan_Kim]

Hi, @Bopps 

Thank you for sharing.

sorry that I cannot understand which column is the correct column for month and year. For instance, when you the very first row in the table, one column says 2021, and the other column says 2020.

[Bopps]

Ignore the first 2 columns and use the  "First Month Year", this was becuase I exported the table from my file into an excel sheet. 

If it is easier to use any other format let me know and I can make the change 

[Jihwan_Kim]

Hi, @Bopps 

Thank you for your feedback.

I assume you want to create a summarized table like below.

Please check the link down below.

https://www.dropbox.com/s/wsitho33vxjm3x0/Sample%20for%20RankX.pbix?dl=0 

Hi, My name is Jihwan Kim.

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

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Bopps]

Hi,

What I am trying to do in the end, is show the sales from only the top 10 TPMNUMBER (Terminals)

On the example you send there is not linking to this field.

I need the top 10 per month per country

Is that possible? 

[Jihwan_Kim · Accepted solution]

Please check the below.

Rank CC =

VAR currentperiod = 'Sample for Power BI'[First Month Year]

VAR currentcountry = 'Sample for Power BI'[First COUNTRY]

RETURN

RANKX (

FILTER (

ALL ( 'Sample for Power BI' ),

'Sample for Power BI'[First Month Year] = currentperiod

&& 'Sample for Power BI'[First COUNTRY] = currentcountry

),

'Sample for Power BI'[Sales Total],

,

DESC

)

New Table =

FILTER (

SUMMARIZE (

'Sample for Power BI',

'Sample for Power BI'[TPMNUMBER],

'Sample for Power BI'[First COUNTRY],

'Sample for Power BI'[First Month Year],

'Sample for Power BI'[Sales Total],

'Sample for Power BI'[Rank CC]

),

'Sample for Power BI'[Rank CC] <= 10

)

https://www.dropbox.com/s/wsitho33vxjm3x0/Sample%20for%20RankX.pbix?dl=0