Forum Discussion

eden's avatar
eden
Icon for Helper I rankHelper I
6 years ago

Report: How to display TOP 5 Result based on Status ?

Hello  i got a sharepoint list similar to follows    Workorder Status customer  start date One published abc 01/08/2020 two reviewed def 24/09/2020 three waiting xyz 17/09/...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi eden 

I build a table like yours to have a test.

I build calculated columns to achieve your goal.

 

Count = CALCULATE(count('Table'[customer ]),FILTER('Table','Table'[customer ]=EARLIER('Table'[customer ])))
Laststatus = CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[customer ]=EARLIER('Table'[customer ])&&'Table'[start date]='Table'[MAXdate]))
MAXdate = MAXX(FILTER('Table','Table'[customer ]=EARLIER('Table'[customer ])),'Table'[start date])
New Value = 
Var _rank2 = RANKX('Table','Table'[MAXdate],,DESC,Dense)
Var _Newvalue = 'Table'[Count]+_rank2*0.0001
return
_Newvalue
Rank = 
Var _Rank = RANKX(FILTER('Table','Table'[customer ]<>"oiu"),'Table'[New Value],,DESC,Dense)
Var _NewRank = IF( 'Table'[Laststatus]<>"published",_Rank,10000)
Return
_NewRank

 

New Table:

 Build Maxdate and Laststatus columns to get the maxdate and laststatus to each customer. Then build a rank column by new value, and let the customer whose laststatus = publish show 10000 in rank.

Result:

You can download the pbix file from this link: Report: How to display TOP 5 result based on status?

 

Best Regards,

Rico Zhou

 

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

eden's avatar
eden
Icon for Helper I rankHelper I
6 years ago

Anonymous 

good one .. I will test my side .

 

I got one more question, In your Ranking ,wat if the customer name  is dynamic ,the customer name can change from the front dynamically and ranking also needs to be changed accordingly

 

Here the customer oiu priority can be changed..how does that  going to work.

Var _Rank = RANKX(FILTER('Table','Table'[customer ]<>"oiu"),'Table'[New Value],,DESC,Dense)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi eden 

    I add some values into the table.

    In this table oiu and ztw 's laststatus = published.

    Change the Rank column:

    Rank = 
VAR _Totaladd1 =
    DISTINCTCOUNT ( 'Table'[customer ] ) + 1
VAR _Rank =
    RANKX (
        FILTER ( 'Table', 'Table'[Laststatus] <> "published" ),
        'Table'[New Value],
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( 'Table'[Laststatus] = "published", _Totaladd1, _Rank )

    Result:

    '

    You can download the pbix file from this link: Report: How to display TOP 5 result based on status?

     

    Best Regards,

    Rico Zhou

     

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