Ranking DAX

[Anonymous]

Hi, I have this table:

- One Year has different Cycles

- There are some Teams with Points in those Cycles

I need to have the ranking of those teams based on the SUM of Points made in those particular Cycles:

In the example, Team B is the FIRST in Cycle 1 and SECOND in Cycle 2 for the same Year.

Which is the best way to have this Rank as a Calculated column?

Thanks

[AntrikshSharma]

Try this:

Rank =
VAR YearSold = Cycles[Year]
VAR CycleNum = Cycles[Cycle]
VAR SameCycles =
    FILTER ( Cycles, Cycles[Year] = YearSold && Cycles[Cycle] = CycleNum )
VAR Result =
    RANKX ( SameCycles, Cycles[Points],, DESC )
RETURN
    Result

[Anonymous]

Thank you, this can work. One detail is that I need to have the cumulate Points in the Year, so in the example, the sum of points to calculate ranking needs to be cumulative for the YEAR:

So the first Cycle of the YEAR stays equal but in Cycle 2 I am summing Cycle 1 & 2 , and so in th Cycle 3 I have to sum Cycles 1,2,3 and so on.

How would be the DAX adjustment?

Thanks a lot

[AntrikshSharma]

Like this?

[Anonymous]

Hi Thanks , is almost like that but the TEAM needs to be consider to SUM the Points of each TEAM in the CUMULATIVE POINTS

[AntrikshSharma]

10+30, 20 +15, 5+2 Is this how you want?

[Anonymous]

Exactly , ranking bases on all the teams in one specific Round and Year

[AntrikshSharma]

That's simple you could use this:

[Anonymous]

But the 40,35,7 values needs to appear only in the CYCLE 2, because the cumulative points are related to Campaign / Cycle/ Team

[AntrikshSharma · Accepted solution]

Like this?

 

[amitchandak]

@Anonymous , Try like

Year cycle team rank= RANKX(FILTER(ALL('Table'[Year],'Table'[Cycle],'Table'[Team]),'Table'[Year]=max('Table'[Year])
,'Table'[Cycle]=max('Table'[Cycle])),calculate(sum(table[CUMULATIVE POINTS])))

[amitchandak]

@Anonymous , this subcategory kind of rank. This series of blogs has an example for both Measure and column

For Rank Refer these links
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