cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Dynamic Top/Bottom N Slicer - Almost have it!

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Dynamic Top/Bottom N Slicer - ¡Casi lo tienes!

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

v-jingzhang_0-1601020926928.png

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.

v-jingzhang_1-1601020926931.png

Best regards

Community Support Team _ Jing Zhang

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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Dynamic Top/Bottom N Slicer - Almost have it!

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

iRe: Dynamic Top/Bottom N Slicer - Almost have it!

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. 

Highlighted
Helper III
Helper III

Re: Dynamic Top/Bottom N Slicer - Almost have it!

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_1-1600808729252.png

 

Highlighted
Microsoft
Microsoft

Re: Dynamic Top/Bottom N Slicer - ¡Casi lo tienes!

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

v-jingzhang_0-1601020926928.png

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.

v-jingzhang_1-1601020926931.png

Best regards

Community Support Team _ Jing Zhang

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

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors