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

[eden]

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/2020
four	defered	abc	01/12/2020
five	published	oiu	01/07/2020
six	in progress	abc	22/05/2019
seven	in progress	def	21/07/2019
eight	in progress	kjs	21/08/2019

I am trying to display something like below in Power BI for top 4 customers whose status is not published

The chart is displaying properly but  i cannot restrict the top 4 customers as all the customers are diplaying in the report

Regards

[Fowmy]

@eden 

Try this measure please

Top4 = 
IF( 
    RANKX( 
        ALLSELECTED('Table'[customer ]),
        CALCULATE(
            COUNTROWS('Table'),
            'Table'[Status] <> "published"
        ),,
        DESC) <5, 
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[Status] <> "published"
    ),
    BLANK()
)

________________________

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]

How to use the below solutution 

Axis : Customer 

Value : Top 4

[Fowmy]

@eden 

Yes, you are right.

________________________

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]

@Fowmy 

Its not doing for me .. i am getting something like below

settings

[Fowmy]

@eden 

You may have more than one customer who falls into the top 4. If you have two customers who are ranked top one, they both come.

________________________

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]

@Fowmy 

that can be the case.

how to filter them based on any other criteria.. We can use start date , that whoever has early entry can be displayed in the chart

[Fowmy]

@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]

@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]

@Fowmy 

table updated with date 

[Anonymous]

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]

@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]

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.