Need help with ranking

[Vish24]

Hello,

I am really struggling in ranking my data. I have data like this:

Name   Sales  Year

A           100    2019

A           50     2019

A           25     2018

B          100    2019

B          75      2018

C          20     2019

B          45     2018

I have Sales by Many people with different years. One Person has many sales in a year. Now, I want to rank them for their sales by year. I am making a slicer for Names and Year. I want to show the ranking as per the selected name and year.

So my data should be rank separately for each year.

Please help! 

[JarroVGIT · Accepted solution]

Hi @Vish24 ,

You can use a measure like this:

Measure = 
VAR _tmpTable = SUMMARIZE(ALLSELECTED(Table1), Table1[Name], "SalesTotal", SUM(Table1[Sales ]))
VAR _rankedTable = ADDCOLUMNS(_tmpTable, "Rank", RANKX(_tmpTable, [SalesTotal], , DESC, Dense))
RETURN
MAXX(FILTER(_rankedTable, [Name] = SELECTEDVALUE(Table1[Name])), [Rank])

Resulting in a visual like this:

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[JarroVGIT · Accepted solution]

Hi @Vish24 ,

You can use a measure like this:

Measure = 
VAR _tmpTable = SUMMARIZE(ALLSELECTED(Table1), Table1[Name], "SalesTotal", SUM(Table1[Sales ]))
VAR _rankedTable = ADDCOLUMNS(_tmpTable, "Rank", RANKX(_tmpTable, [SalesTotal], , DESC, Dense))
RETURN
MAXX(FILTER(_rankedTable, [Name] = SELECTEDVALUE(Table1[Name])), [Rank])

Resulting in a visual like this:

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Vish24]

@JarroVGITThank you! Its working perfectly. I was making some typing mistake. Just need one more help. I want to show the rank in card after selecting name and year from slicer. Rank is coming 1 for all selected names. Its coming correctly in the table. Thank you!

[JarroVGIT]

Measure =
VAR _tmpTable = SUMMARIZE(ALL(Table1), Table1[Name], Table1 [Year], "SalesTotal", SUM(Table1[Sales ]))
VAR _rankedTable = ADDCOLUMNS(_tmpTable, "Rank", RANKX(_tmpTable, [SalesTotal], , DESC, Dense))
RETURN
MAXX(FILTER(_rankedTable, [Name] = SELECTEDVALUE(Table1[Name]), [Year] = SELECTEDVALUE(Table1[Year])), [Rank])

That should work:)

[Vish24]

@JarroVGIT I sent you private message. Please have a look and let me know whenever you get time. I am getting some error in this. I sent the screenshot for the same.

Thank you!

[Ashish_Mathur]

Hi,

So if you select one year and one person, you want to know the rank of that one chosen person in that one chosen year in a card visual.  Am i correct?  Share some sample data that i can paste in an Excel workbook.

[Vish24]

@Ashish_Mathur  My data is like this:

Name	Sales	Year
A	200	2018
A	26	2019
B	3456	2018
B	55555	2019
B	4
C	5	2019
C	55	2018
A	767	2019
C	888	2018
D	899	2019

I have multiple sales by each person. So I am ranking them for each year. So I have a slicer with name and year. I want to show their ranking in a card as per each year and if year is not selected then combina ranking for 2018+2019 sales. 

[Ashish_Mathur]

Hi,

Create a slicers for Year and Name.  Write this measure

Total Sales = SUM(Data[Sales])

Rank = RANKX(ALL(Data[Name]),[Total Sales])

Hope this helps.

[Vish24]

@Ashish_Mathur thanks for your input but its not working. I have made a table with name, total sales and rank. Its ranking corrrctly in that. But when i am selecting name from slicer I am getting different rank in card. Even if I am selecting name from table its showing different rank in card. So in my table its rank 107 for a name but if i select that name then in card its showing 99.

[Ashish_Mathur]

Hi,

May be I am not clear with your requirement but please let me know the problem that you face in this PBI file.

[Vish24]

@Ashish_Mathur  yes...thats corrcet

[Vish24]

@JarroVGIT   I tried this and I am getting the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." 

[JarroVGIT]

Please show screenshot of the formula bar, are you getting any red didly lines?

[Vish24]

No, not getting red lines

[JarroVGIT]

Well, then there must be something going wrong in translating my example into your real case; after all it does work 😛

If you want I can have a look at your PBIX (please remove confidential data). Alternatively, post your formula here (as you have it now) and a screenshot of the Table in question. Perhaps there is just something going wrong in subsituting my example columns with your real columns?

[amitchandak]

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Check for "Ranking by Sub Category"

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Vish24]

Thank you! I am looking into it and will confirm if I would be able to do my ranking.

[amitchandak · Accepted solution]

Check

https://www.dropbox.com/s/ps1475oidzubmud/yearsalesemployerank.pbix?dl=0

I think category rank will do

Rank = RANKX(ALL(yearsalesemployerank[Name]),CALCULATE(SUM(yearsalesemployerank[Sales])),,DESC,Skip)

[Vish24]

@amitchandakThank you so much! Its working exactly I want. Just need one last help..I need to exclude one name from the Names List for the ranking. I am trying AllExcept but then my ranking is not working correctly. Please let me know the syntax to exclude one name from my list.

Thank you!

[amitchandak]

Try excluding from visual

[Vish24]

@amitchandak I tried but suppose if ranking is 1 for that name then my ranking is showing from 2 only..