Forum Discussion
Solved
Rank by group and sum
Hi, I have difficulties to calculate the rank over my table with a sum. Here my fact table : year_quarter country value 2024-Q1 US 10 2024-Q1 US 14 2024-Q1 Belgium 23 2024...
Hi Anonymous ,
Your reply helped me, I just adjusted it a little bit to sum by quarter and country :
rank = RANKX(FILTER('Table',[year_quarter]=EARLIER('Table'[year_quarter])),SUMX(FILTER('Table',[year_quarter]=EARLIER('Table'[year_quarter])&&[country]=EARLIER('Table'[country])),[value]),,ASC,Dense)Thanks a lot !
Regards.
Gabry
Super User
Super UserHello Rinn
here is your formula:
Rank Measure =
VAR CurrentYearQuarter = SELECTEDVALUE('Table'[year_quarter])
VAR CurrentCountry = SELECTEDVALUE('Table'[country])
VAR CurrentTotalValue =
CALCULATE(
SUM('Table'[value]),
'Table'[year_quarter] = CurrentYearQuarter,
'Table'[country] = CurrentCountry
)
VAR RankingTable =
SUMMARIZE(
ALL('Table'),
'Table'[year_quarter],
'Table'[country],
"TotalValue", SUM('Table'[value])
)
RETURN
RANKX(
FILTER(
RankingTable,
[year_quarter] = CurrentYearQuarter
),
[TotalValue],
CurrentTotalValue,
DESC,
DENSE
)
let me know if this help 😉
let me know if this help 😉
Hello Gabry !
Thanks for your reply but your solution does not seem to work, it displays just 1 everywhere...