Forum Discussion

Rinn's avatar
Rinn
Frequent Visitor
2 years ago
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...
Need Help
  • Rinn's avatar
    Rinn
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Gabry ,

 

If you want a calculated column, please try:

 

rank = RANKX(FILTER('Table',[year_quarter]=EARLIER('Table'[year_quarter])),SUMX(FILTER('Table',[country]=EARLIER('Table'[country])),[value]),,ASC,Dense)

 

Result:

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Rinn's avatar
Rinn
Frequent Visitor
2 years ago

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.