Forum Discussion

Fantmas's avatar
Fantmas
Icon for Helper III rankHelper III
3 years ago

Rankx variation in %

Hi all,

I attempt to rank a table "Fact FTE" based on measure which calculate the variation between current and previous month,

When I used rank function, it returns me all ranked as 1 when the % are different see below and I do not understand why

Measure I have created :

 

Δ by Mth in % = 

Var Last_Mth = CALCULATE(SUM('Fact FTE'[Actual Work FTE]), PARALLELPERIOD('DimDate FTE'[Date],-1,MONTH))

Var Mth = CALCULATE(SUM('Fact FTE'[Actual Work FTE]))

RETURN

DIVIDE(Mth - Last_Mth, Last_Mth,0)

 

 

 

Rank Evolution = 

RANKX('Fact FTE', [Δ by Mth in %],,DESC)

 



Thank you for your support,

Regards


Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Fantmas , Try like

     

    RANKX(allselected('Fact FTE'), [Δ by Mth in %],,DESC)

     

    The above will work best with the lowest level of the table

     

    If you use one column in Ranxk

    if you add any other column in Ranks, Rank will distribute inside that one level 0, level 1 and contract type , if

     

    You have multiple columns like

    refer : https://youtu.be/cN8AO3_vmlY?t=25635

    Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

     

    • Fantmas's avatar
      Fantmas
      Icon for Helper III rankHelper III
      3 years ago

      Hi Amit,

      I had a look to the video you shared with me, now I still have duplicate but based on the tuto you provide I should have only a unique value did I miss something ?

       

      Rank Evolution = 

VAR T1 = ADDCOLUMNS(SUMMARIZE(FILTER(ALLSELECTED('Fact FTE'), RELATED('DimDate FTE'[Date])>= Date(2021,1,31)), 'DimDate FTE'[Date], DimOrgUnit[Level 0], DimOrgUnit[Level 1], DimEmployee[Macro Contract Type],"TOTAL FTE", SUM('Fact FTE'[Actual Work FTE])),"Variation by Month", [Δ by Mth in %])

RETURN
IF (
NOT ( [Δ by Mth] = BLANK () ),
RANKX ( FILTER ( T1,[Variation by Month]<>BLANK() && [TOTAL FTE] <> BLANK()),[Variation by Month],
[Δ by Mth in %],
DESC,
DENSE
)
)

       

      Thank you for your feedback amitchandak 

      Regards