Forum Discussion

gambleave's avatar
gambleave
Icon for Helper II rankHelper II
4 years ago
Solved

RANKX with table aggregation

Looking for help with the RANKX function, where table aggregation is required as an intermediate step. 

With a data table as illustrated below, I want to achieve a rank of Business Group based on the total activity duration associated with each Business Group. There are some duplicated Activity ID records in the data set to attribute multiple employees in some activity records. 

 

I was thinking it involved a SUMMARIZE measure nested in the RANKX, but can't seem to get this working. Help appreciated. 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your expected outcome looks like, but please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

    Rank by activity duration measure: = 
IF (
    HASONEVALUE ( Data[Business Group] ),
    RANKX (
        ALL ( Data[Business Group] ),
        CALCULATE ( SUM ( Data[Activity Duration] ) ),
        ,
        DESC
    )
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your expected outcome looks like, but please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

    Rank by activity duration measure: = 
IF (
    HASONEVALUE ( Data[Business Group] ),
    RANKX (
        ALL ( Data[Business Group] ),
        CALCULATE ( SUM ( Data[Activity Duration] ) ),
        ,
        DESC
    )
)