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/...
Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

@eden

I gave you the solution based on the sample data you shared and there was no date column to be included in the rank. Share a propper sample and show the expected output.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube Linkedin

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

Fowmy 

 

My intention was to filter the top 5 records based on their weightage in my whole table.. If two records carry same weightage we can use someother field to decide the winner.. 

 

Here we can use start date as a criteria  as early start date will have highest priority in the chart

 

I am expecting something like below, Customer XYZ is not eligible as OIU,KJS as earlier start date

 

 

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

    Fowmy 

     

    table updated with date 

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