Top N with Slicer and Steps

[TimoRiikonen]

There are several instructions on the internet on how to create a dynamic solution that shows only the first top N results and many of these also have the "Others" category.

This version shows both the slider and selection list options, so you can choose the one you need.

It is easiest if you first download the solution, so you can compare instructions against the solution.

Background: This graph shows how many sales each person on the sales team has made.

These solutions also have three extra features copied from elsewhere:

• Calculation of the data update. Timezone is hard coded, so change that.
• Automated conversion of data content types
• Automated conversion of column names from FirstName to First Name.

For Top N, you need to do the following:

• You must have a sum value in the content. In my example I created measure Sum of SalesYTD = SUM('Sales vSalesPerson'[SalesYTD])
• For slider solution:
  1. I suggest that you count also the maximum value for the slider. TopNMax = Count('Sales vSalesPerson'[First Name])
  2. Create new parameter with Modeling -- > New Parameter to "nowhere" so it will create a table as well: TopN Slicer.
  3. If you have a lot of columns, you may consider using larger steps than one on the slicer.
  4. Either create the slicer at the same time, or copy it from the example solution.
  5. If you didn't add auto-calculated maximum value yet, then replace the hard-coded max value with [TopNMax] measure.
• For selection list solution:
  1. Create a new empty table and column TopN Steps = {3, 5, 10, 20}
  2. Create or copy the selection list on your graph.
• For both of the solutions, we need to create DAX union for the Others calculation. Press Create New [DAX] Table and insert:
  TopNUnion = UNION(ROW("Key Values", -99, "Display Values", "Others"), ALLNOBLANKROW('Sales vSalesPerson'[Business Entity ID],'Sales vSalesPerson'[First Name]))
  1. First row second value can be anything except one of the key values.
  2. If you change the word "Others", you need to also change the code . We want to show first names on our graphs, so that goes as the second value on the second line.
  3. Second line first value is the key for the name. In my example, this would be Employee ID on most databases, but, in this database, it is Business Entity ID.
• Next, let's create functions to select top N and calculate sum of those that were excluded:

Top X Steps =
    VAR TopNumber = IF(HASONEVALUE('TopNSteps'[TopN]),VALUES('TopNSteps'[TopN]),8)
    VAR Rest =
        COUNTROWS ( 'Sales vSalesPerson' ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( TopNUnion[Key Values] ),
            CALCULATE (
                [Sum of SalesYTD],
                FILTER (
                    'Sales vSalesPerson',
                    [Business Entity ID] = VALUES ( TopNUnion[Key Values] )
                        && CONTAINS (
                            TOPN (
                                TopNumber,
                                ADDCOLUMNS (
                                    ALL ( TopNUnion[Key Values] ),
                                    "SalesYTD", CALCULATE (
                                        [Sum of SalesYTD],
                                        FILTER ( 'Sales vSalesPerson', 'Sales vSalesPerson'[Business Entity ID] = EARLIER ( [Key Values] ))
                                    )
                                ),
                                [SalesYTD], DESC
                            ),
                            TopNUnion[KeyValues], VALUES (TopNUnion[KeyValues])
                        )
                )
            )
        )

• [SalesYTD] refers to 'Sales vSalesPerson'[SalesYTD]. Replace it in similar manner with the value column.
• And create another for Others

Other Steps =
VAR TopNumber =
    IF ( HASONEVALUE ( 'TopNSteps'[TopN] ), VALUES ( 'TopNSteps'[TopN] ), 7 )
VAR Rest =
    COUNTROWS ( 'Sales vSalesPerson' ) - TopNumber
RETURN
    IF (
        HASONEVALUE ( TopNDaxUnion[Key Values] ),
        IF (
            VALUES ( TopNDaxUnion[Display Values] ) = "Others",
            SUMX (
                TOPN (
                    Rest,
                    ADDCOLUMNS (
                        VALUES ( 'Sales vSalesPerson'[Business Entity ID] ),
                        "SalesYTD", [Sum of SalesYTD]
                    ),
                    [SalesYTD],ASC
                ),
                [SalesYTD]
            )
        )
    )

• For the slider version, replace 'TopNSteps'[TopN] with 'TopNSlider'[TopN], so, if you want to have both versions, you need four functions:

• Add Display Values to Axis.

• Add Top X and Other on Value. Make sure that they are in this order, so the Others comes last. If you don't wish to have the Others at all, then just leave it out.

• Improve the looks. I have used only standard features to do it. Remember that the Format Painter button exists.

TopN Slider and Steps.pbix