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_quartercountryvalue
2024-Q1US10
2024-Q1US14
2024-Q1Belgium23
2024-Q1Australia34
2024-Q2Belgium43
2024-Q2US23

(note that country comes from a another dim table dim entity)

 

Expected output : (mandatory Rank is a calculated column)

year_quartercountrysum(value)rank
2024-Q1US242
2024-Q1Belgium233
2024-Q1Australia341
2024-Q2Belgium431
2024-Q2US232

 

I tried this calculated column but the sum does not seem to work :

Rank =
VAR tab =
    SUMMARIZE (
         ALL ( 'table' ),
        'table'[year_quarter],
        'dim entity'[country],
        "Total Value", SUM ( 'table'[value] )
    )
RETURN
    RANKX ( tab, 'table'[year_quarter] = EARLIER('table'[year_quarter]), , DESC, DENSE )
 
Whats wrong with my DAX column ?
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.

7 Replies

  • 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.

  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    2 years ago

    Hello 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 😉
    • Rinn's avatar
      Rinn
      Frequent Visitor
      2 years ago

      Hello Gabry !

      Thanks for your reply but your solution does not seem to work, it displays just 1 everywhere...

       

       

      • Gabry's avatar
        Gabry
        Icon for Super User rankSuper User
        2 years ago

        Strange, to me it works, as a measure:


         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Why are you solving this with a calculated column formula?  Why not with a measure?