Top N with Parameter?

[captainlaw]

Hello Community,
I’m curious if there's a workaround to pass a parameter for Top N filtering? I haven’t seen a good example, so wondering if you know some resources on this.  Customer would like to select N number of legends to be displayed in a chart.

Thanks.

[OwenAuger]

@captainlaw

Here's an example from an earlier post, where N is selected by slicer

http://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/52120#M20971

Is that the sort of thing you were looking for?

[captainlaw]

Hello,

I referenced your other post, but I think my scenario might be slightly different.  I've attached my file.

The chart on the right (DAX) is what I would like to match up with the chart on the left.  By the user selection (5,10,50,100), the chart would render N number of States within the chart.  I must have left out some isfiltered() condition somewhere that I am not sure how to resolve.

Your help is appreciated.

https://1drv.ms/u/s!AqYu51c7j3dhhQuvGEksGk-UC8Ax

[captainlaw]

I also tried the route of rankx, but for some reason it's not producing the correct rank.  New column is named LawRank. 

https://1drv.ms/u/s!AqYu51c7j3dhhQxNXnvVBShgD-75

[OwenAuger]

Hi @captainlaw

Just a couple of tweaks required.

I've fixed the measure so that it replicates the behaviour of the visual level TopN filter, and named the measure MyTopN3.

PBIX here

MyTopN3 = 
CALCULATE (
    SUM ( LAWCrime[Violent crime total] ),
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN (
                [SelectedN],
                ALLSELECTED ( LAWCrime[State] ),
                CALCULATE ( SUM ( LAWCrime[Violent crime total] ) )
            ),
            ALLSELECTED ( LAWCrime[Year] )
        )
    )
)

Key points:

1. TOPN 2nd argument: Used ALLSELECTED ( LAWCrime[State] ) within TOPN. This is because you want to see the TopN from States selected on the slicer rather than all States.
2. TOPN 3rd argument: Wrapped in CALCULATE. The expression in the 3rd argument is calculated in the row context of each row of the table in the 2nd argument. CALCULATE (or a measure with an implied CALCULATE) is needed for context transition (to convert the row context to filter context), to correctly calculate the SUM in the context of the current State.  
3. Wrapped the TOPN in a CALCULATETABLE(..., ALLSELECTED ( LAWCrime[Year] ) ). This ensures the States are ranked based on their total over the entire range of years selected (same as the chart on the left).

Regds,

Owen

[captainlaw]

You are very helpful Owen.

If I have additional relationships to calculate the Top N, such as City or Gender... etc, do I simply add it to the end of this clause -

ALLSELECTED ( LAWCrime[Year]

,ALLSELECTED ( LAWCrime[Gender] 

,ALLSELECTED ( LAWCrime[City] 

... etc.

Thank you!

[OwenAuger · Accepted solution]

@captainlaw

You would only need to add additional ALLSELECTED parameters to CALCULATETABLE if you are putting those additional columns on axes (or rows/columns) of the visual itself. If you are simply adding more slicers, then the existing measure would be fine.

However, a better way of defining the measure to capture all possibilities would be:

MyTopN3 = 
CALCULATE (
    SUM ( LAWCrime[Violent crime total] ),
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN (
                [SelectedN],
                VALUES ( LAWCrime[State] ),
                CALCULATE ( SUM ( LAWCrime[Violent crime total] ) )
            ),
            ALLSELECTED ()
        )
    )
)

This uses ALLSELECTED () without arguments to ensure that the ranking is carried at the total level of the visual (this also means you can change ALLSELECTED ( LAWCrime[State] ) to VALUES ( LAWCrime[State] ).

If you want to rank by something other than state then just change the red part.

Cheers,

Owen 🙂

[Anonymous]

@OwenAuger Thanks for sharing this. what if I want to group the remaining as Others if they are more than SelectedN, how do I achieve that. 

Thanks!

[OwenAuger]

@Anonymous yes that can be done 🙂

There are a number of ways of handling TopN + Other - how exactly would you want this to appear in your visuals?

e.g. For the top 5 would you want to see:

Item 1

Item 2

Item 3

Item 4

Item 5

Other

[Anonymous]

I will like it to appear like mentioned:

Item 1

Item 2

Item 3

Item 4

Item 5

Others

Also, I want to have a scenario where if I hover over any of the values above, it will show the value and the measure. 

Eg. If I hover over Item 1, I will see Item 1 and the corresponding value for Item 1 and then if I hover others, I will see all the Items that were grouped as Others and their values.

[OwenAuger]

@Anonymous 

Just catching up on replying to this one.

I would suggest an approach such as the one in this article for setting up the data model.

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/ 

Please post back if needed.

Regards,

Owen

[Ouail]

Hello Owen,

Is there a way to parametrize the red part using a slicer ? To change the "subject" of the Top N.
Use case : I have multiple bar charts with different axis columns where I want to use the same TopN measure.

Regards,

Ouail

[OwenAuger]

@Ouail yes it is possible. The way I have done it in the past requires creating an "unpivoted" version of the table for the entity in question. This unpivoted table the contains all attribute-value pairs for each row of the original table.

This article contains a good example:

http://tinylizard.com/dynamically-changing-chart-axis/ 

That article was written in the context of Power Pivot which doesn't allow bidirectional relationships. But since Power BI does allow bidirectional relationships, you can extend the method in that article by creating a bidirectional relationship between ProductMaster and ProductUnpivot (using table names from the article).

Kind regards,

Owen