Forum Discussion
Simple Ranking
Hi Experts,
Im a newbie here, so I tried some online article, but failed 😕
I am looking to create a ranking for each year, per Customer based on Sale Size for each Sale Type:
Sample Table and Ranking ( I repeat every year the same, but of course they could have been different 🙂 )
| Date | Customer | Sale Type | Sale Size | Ranking |
| 2022 | 1 | 1 | 10 | 3 |
| 2022 | 2 | 1 | 15 | 1 |
| 2022 | 3 | 1 | 13 | 2 |
| 2022 | 1 | 2 | 20 | 3 |
| 2022 | 2 | 2 | 22 | 2 |
| 2022 | 3 | 2 | 25 | 1 |
| 2022 | 1 | 3 | 36 | 1 |
| 2022 | 2 | 3 | 34 | 3 |
| 2022 | 3 | 3 | 35 | 2 |
| 2021 | 1 | 1 | 10 | 3 |
| 2021 | 2 | 1 | 15 | 1 |
| 2021 | 3 | 1 | 13 | 2 |
| 2021 | 1 | 2 | 20 | 3 |
| 2021 | 2 | 2 | 22 | 2 |
| 2021 | 3 | 2 | 25 | 1 |
| 2021 | 1 | 3 | 36 | 1 |
| 2021 | 2 | 3 | 34 | 3 |
| 2021 | 3 | 3 | 35 | 2 |
| 2020 | 1 | 1 | 10 | 3 |
| 2020 | 2 | 1 | 15 | 1 |
| 2020 | 3 | 1 | 13 | 2 |
| 2020 | 1 | 2 | 20 | 3 |
| 2020 | 2 | 2 | 22 | 2 |
| 2020 | 3 | 2 | 25 | 1 |
| 2020 | 1 | 3 | 36 | 1 |
| 2020 | 2 | 3 | 34 | 3 |
| 2020 | 3 | 3 | 35 | 2 |
this works for a Table visual:
Table Rank = if(ISINSCOPE('Table'[Date]),RANKX(ALLSELECTED('Table'[Date],'Table'[Customer],'Table'[Sale Type]),calculate(sum('Table'[Sale Size])),,DESC,Dense))This works for a matrix visual
Rank =
VAR yearrank = RANKX(ALLSELECTED('Table'[Date]),Calculate(SUM('Table'[Sale Size])),,DESC,Dense)
VAR custrank = RANKX(ALLSELECTED('Table'[Customer]),Calculate(SUM('Table'[Sale Size])),,DESC,Dense)
VAR typerank = RANKX(ALLSELECTED('Table'[Sale Type]),Calculate(SUM('Table'[Sale Size])),,DESC,Dense)
RETURN
SWITCH(TRUE(),ISINSCOPE('Table'[Sale Type]),typerank,ISINSCOPE('Table'[Customer]),custrank,
ISINSCOPE('Table'[Date]),yearrank)
7 Replies
hope this video helps:
Almost! Actually a better vid from here would be: https://www.youtube.com/watch?v=y2FFjfRD-Bo
however, the ranking is not really working. Not sure if this is because I am trying to filter on the date as well...
Total Size = SUM(Table[Size])
Ranking = RANKX (ALLSELECTED(Table[Customer]),[Total Size],,DESC,Dense)this works for a Table visual:
Table Rank = if(ISINSCOPE('Table'[Date]),RANKX(ALLSELECTED('Table'[Date],'Table'[Customer],'Table'[Sale Type]),calculate(sum('Table'[Sale Size])),,DESC,Dense))This works for a matrix visual
Rank =
VAR yearrank = RANKX(ALLSELECTED('Table'[Date]),Calculate(SUM('Table'[Sale Size])),,DESC,Dense)
VAR custrank = RANKX(ALLSELECTED('Table'[Customer]),Calculate(SUM('Table'[Sale Size])),,DESC,Dense)
VAR typerank = RANKX(ALLSELECTED('Table'[Sale Type]),Calculate(SUM('Table'[Sale Size])),,DESC,Dense)
RETURN
SWITCH(TRUE(),ISINSCOPE('Table'[Sale Type]),typerank,ISINSCOPE('Table'[Customer]),custrank,
ISINSCOPE('Table'[Date]),yearrank)
I don't see Sales_Type. Please provide sample data that matches your description. Please indicate expected outcome.
Sorry for the confusing post, Ive re-edit, which should make it more clear.
