Dynamic Top/Bottom N Slicer - Almost have it!

[KMcCarthy9]

Hi All, I have been stuck on this for days now and I think I am closer but still do not have it figured out.

I have a visual with a list of vendors, MTTR Days value (lower is better), and count of work order. 
I have created a Select Top N table with a column holding values 5, 10, 25, 50, etc...
Within this table I have the column: 

Selected N = SELECTEDVALUE('Select Top N'[Select Top N])

 I have also created a measure MTTR Days Top N Vendors:

MTTR Days Top N Vendors = IF([Selected N]=BLANK(),AVERAGE(data[ETTR Days]),

CALCULATE(DATA[MTTR DAYS], TOPN('Select Top N'[Selected N], data, [Work Order Vendor], ASC, data[MTTR DAYS])))

So I have my visual and my slicer list of the values I input. However, the number of vendors is not changing with the selection of a value in the slicer. It does change the values sometimes though and I do not know why? 

My ultimate goal is to the entire list of vendors showing, then a user can choose to see Top 5, 10, etc, vendors with the lowest MTTR Days (lower is better). I would like to also have the user be able to choose to only look at vendors with at least X number of workorders. I have this in place with a slicer using the measure:

MTTR Slider = CALCULATE(COUNTA(data[ETTR Days]), FILTER(DATA, DATA[Work Order Vendor]=EARLIER(DATA[work order vendor])))

I ave attached a trimmed down .pbix file here: .pbix file.

The ultimate question I want a user to be able to answer is...who are the X number of vendors with the lowest MTTR days while also having at least X numberfo work orders. 

Thank you in advance!

[v-jingzhang · Accepted solution]

Hello @KMcCarthy9

I create a table summarizing the data table by work order provider and filter providers with MTTR NULL days as below screenshot shown, and then I use this table to get the primary N values.

Table =
FILTER (
    SUMMARIZE (
        data,
        data[Work Order Vendor],
        "MTTR Days", AVERAGE ( data[ETTR Days] ),
        "WO Count", COUNT ( data[WorkOrder Number] ),
        "MTTR Slider", COUNT ( data[ETTR Days] )
    ),
    NOT ( ISBLANK ( [MTTR Days] ) )
)

And then create the following measures:

Measure MTTR Days = SUM ( 'Table'[MTTR Days] )

Measure WO Count = SUM('Table'[WO Count])

top N MTTR Days =
IF(ISBLANK([Selected N]),
[Measure MTTR Days],
CALCULATE (
    [Measure MTTR Days],
    TOPN ( [Selected N], ALL ( 'Table'[Work Order Vendor] ), [Measure MTTR Days], ASC ),
    VALUES ( 'Table'[Work Order Vendor] )
))

top N WO Numbers = IF ( ISBLANK ( [top N MTTR Days] ), BLANK (), [Measure WO Count] )

Finally, place them in the table visual.

Best regards

Community Support Team _ Jing Zhang

If this post helps,please consider Accepting it as the solution to help other members find it.

[amitchandak]

@KMcCarthy9 ,

I tried it like this and it working.

MTTR Days Top N Vendors = 

CALCULATE([MTTR DAYS], TOPN([Selected N],ALLSELECTED( data[Work Order Vendor]), data[MTTR DAYS], ASC), VALUES(data[Work Order Vendor]))

All measure on the table has to follow this approach of using top N. Or use TOP as a filter or this measure not blank as a filter

File Attached after signature

[KMcCarthy9]

Hi @amitchandak,
I have uplaoded another file showing some of my issue. I added vendors that have NULL for MTTR Days. When selecting the TOP N, I do not want these to be included. Currently in the file, I have Top 5 selected, and it is showing me 3 vendors with NULL MTTR Days. 

How do I adjust my DAX to not include these?

I really appreciate the help! 

.pbix file 

[KMcCarthy9]

Hi @amitchandak 

This is working beautifully on the test .pbix file, however my real data model has a lot more than 10 vendors and I cannot get it to work, even though everything is the same. When I choose 5, 10, 25...the visual is just blank - shows nothing at all. What am I doing wrong? 

There are some vendors that have BLANK or NULL for MTTR days. I would want those excluded and only count vendors where MTTR value is at least 0.