cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bopps
Frequent Visitor

Rank X by Sales Total by Month Year and Country

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 

 

Bopps_1-1621330557735.png

 

Then created a rankx but this ranks everything

 

Bopps_2-1621330598954.png

 

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 

 

 

 

1 ACCEPTED SOLUTION

Please check the below.
 
Picture4.png
 
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
)
 
 
 

 

 

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.


Go to My LinkedIn Page


View solution in original post

10 REPLIES 10
Bopps
Frequent Visitor

Thank you so much, this seem to be working. 

 

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

 

Jihwan_Kim
Super User
Super User

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
)
 
 
Picture3.png
 
 
 

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

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.


Go to My LinkedIn Page


Bopps
Frequent Visitor

@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"

 

Bopps_0-1621333693841.png

 

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.

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.


Go to My LinkedIn Page


Bopps
Frequent Visitor

@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 

 

 

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.

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.


Go to My LinkedIn Page


Bopps
Frequent Visitor

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 

 

 

Hi, @Bopps 

Thank you for your feedback.

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

Please check the link down below.

 

Picture2.png

 

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

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.


Go to My LinkedIn Page


Bopps
Frequent Visitor

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? 

 

Please check the below.
 
Picture4.png
 
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
)
 
 
 

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!