Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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! 🙂
Proud to be a Super User!
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)
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! 🙂
Proud to be a Super User!
@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!
Proud to be a Super User!
@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!
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.
@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.
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.
@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.
Hi,
May be I am not clear with your requirement but please let me know the problem that you face in this PBI file.
@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."
Please show screenshot of the formula bar, are you getting any red didly lines?
Proud to be a Super User!
No, not getting red lines
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?
Proud to be a Super User!
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
Thank you! I am looking into it and will confirm if I would be able to do my ranking.
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)
@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!
Try excluding from visual
@amitchandak I tried but suppose if ranking is 1 for that name then my ranking is showing from 2 only..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |