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.
Hi I have a base table as seen below.
date | name | Value |
01-01-2020 | A | 1 |
01-01-2020 | A | 16 |
01-01-2020 | A | 4 |
01-01-2020 | B | 1 |
01-01-2020 | B | 9 |
01-01-2020 | C | 19 |
02-01-2020 | A | 12 |
02-01-2020 | B | 14 |
02-01-2020 | C | 7 |
02-01-2020 | C | 9 |
02-01-2020 | D | 4 |
I need to rank each name for each date after adding all values for that name for a particular date. Below will be the expected output.
Date | Name | Value | Rank |
01-01-2020 | A | 21 | 1 |
01-01-2020 | B | 10 | 3 |
01-01-2020 | C | 19 | 2 |
02-01-2020 | A | 12 | 3 |
02-01-2020 | B | 14 | 2 |
02-01-2020 | C | 16 | 1 |
02-01-2020 | D | 4 | 4 |
I tried using rankx but not able to do. Any idea how to achieve this?
Solved! Go to Solution.
@Anonymous - I did it like this:
Measure 23 =
VAR __Date = MAX('Table (23)'[date])
VAR __Name = MAX('Table (23)'[name])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table (23)'),[date] = __Date),
[name],
"Value",
SUM([Value])
)
VAR __Table1 = ADDCOLUMNS(__Table,"Rank",RANKX(__Table,[Value]))
RETURN
MAXX(FILTER(__Table1,[name] = __Name),[Rank])
PBIX is attached below sig, you want Page 23, Table (23) and Measure 23.
@Anonymous - I thought you wanted a rank for each date and for each Name like what you showed in the example output you wanted (below). If you don't want that, get rid of the FILTER for date = __Date in your SUMMARIZE
Date | Name | Value | Rank |
01-01-2020 | A | 21 | 1 |
01-01-2020 | B | 10 | 3 |
01-01-2020 | C | 19 | 2 |
02-01-2020 | A | 12 | 3 |
02-01-2020 | B | 14 | 2 |
02-01-2020 | C | 16 | 1 |
02-01-2020 | D | 4 |
4 |
@Anonymous , in case you need measure rank
rankx(all(Table[Name]),calculate(sum(Table[Value])))
In case you need a column or other option refer
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Please try this measure expression
Rank =
RANKX ( ALL ( 'Rank'[name] ), CALCULATE ( SUM ( 'Rank'[Value] ) ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Anonymous - I did it like this:
Measure 23 =
VAR __Date = MAX('Table (23)'[date])
VAR __Name = MAX('Table (23)'[name])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table (23)'),[date] = __Date),
[name],
"Value",
SUM([Value])
)
VAR __Table1 = ADDCOLUMNS(__Table,"Rank",RANKX(__Table,[Value]))
RETURN
MAXX(FILTER(__Table1,[name] = __Name),[Rank])
PBIX is attached below sig, you want Page 23, Table (23) and Measure 23.
@Greg_Deckler - Thanks this worked perfectly fine for me. But when I select multiple dates, it gives rank for each date and not for multiple dates combined. I have a date filter in which user can select more than 1 date and then it does not give desired result. Any idea what tweakwill I have to do in code so that it works for multiple dates as well.
Thanks,
Amit Darak
@Anonymous - I thought you wanted a rank for each date and for each Name like what you showed in the example output you wanted (below). If you don't want that, get rid of the FILTER for date = __Date in your SUMMARIZE
Date | Name | Value | Rank |
01-01-2020 | A | 21 | 1 |
01-01-2020 | B | 10 | 3 |
01-01-2020 | C | 19 | 2 |
02-01-2020 | A | 12 | 3 |
02-01-2020 | B | 14 | 2 |
02-01-2020 | C | 16 | 1 |
02-01-2020 | D | 4 |
4 |
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |