Nth Top Salesperson

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

Back in September 2017, Reid Havens wrote a nice blog post that followed up upon three earlier posts by Rob (https://powerpivotpro.com/2017/09/dax-reanimator-series-top-performers-using-summarize/).  The posts are related to returning the name (i.e. text) of a top-performer, rather than simply the top-performance value.

The question I’m wondering about is, how would one go about returning the name of the nth top-performer?

Consider a measure returning the top-performer’s name:

FirstPlaceSalesperson:=CALCULATE(
IF(
HASONEVALUE(Dim_Salespersons[FullName]),
VALUES(Dim_Salespersons[FullName]),
COUNTROWS(VALUES(Dim_Salespersons[FullName]))
& ” Salespersons Tied”
),
TOPN(1,Dim_Salespersons,SUM(Fact_Transactions[Revenue]))
)

How might one go about writing a measure to return the NthPlaceSalesperson (e.g. “ThirdPlaceSalesperson”)?

Thank you!

Ron

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

Well that was an interesting challenge for me.  Below is the solution I came up with to find the nth top-performing salesperson.  In the example, I want to determine the name the 3rd place salesperson.

ThirdPlaceSalesperson:=MINX(
FILTER(
VALUES(Dim_Salespersons[FullName]),
RANKX(VALUES(Dim_Salespersons[FullName]),SUM(Fact_Transactions[Revenue])) = <span style=”text-decoration: underline;”>3</span>
),
CALCULATE(
IF(
HASONEVALUE(Dim_Salespersons[FullName]),
VALUES(Dim_Salespersons[FullName]),
COUNTROWS(VALUES(Dim_Salespersons[FullName]))
& ” Salespersons Tied”
)
)
)

 

Hope this helps others,

Ron

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

Ooops… I noticed there was some html in the above example, plus a missing CALCULATE().  Here’s the final final:

ThirdPlaceSalesperson:=MINX(
FILTER(
VALUES(Dim_Salespersons[FullName]),
RANKX(VALUES(Dim_Salespersons[FullName]),CALCULATE(SUM(Fact_Transactions[Revenue]))) = 3
),
CALCULATE(
IF(
HASONEVALUE(Dim_Salespersons[FullName]),
VALUES(Dim_Salespersons[FullName]),
COUNTROWS(VALUES(Dim_Salespersons[FullName]))
& ” Salespersons Tied”
)
)
)

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Ron,

Here is a hint for a challenge from Rob for perhaps a more readable solution:

Take the TOPN ( 1, ) ascending of the TOPN ( 3, ) descending and you will get the 3rd.

I have also seen Rob use LASTNONBLANK as well as VALUES to return a name in a measure.

If you could attach some sample data in a workbook, would be glad to write a sample formula for others following this post.

Tom

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

Hi Tom!

Hmmm… the TOPN(1, ) in ascending order of the TOPN(3, ).  I wouldn’t have thought of that.  🙂

Attached is a workbook with sample data and the solution I came up with.  There are two measures included.  One specifically calculates the 3rd top salesperson.  The other measure is essentially the same with the exception that it takes advantage of a disconnected table for use in a pivot table.

Ron

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Ron,

Had a good chuckle at salesperson names!

Please see attached workbook. Instead of VALUES or LASTNONBLANK, I opted to use CONCATENATEX which also plays nicely with single values (hopefully you have Excel 2016).

Third in Sales:=CONCATENATEX (
    TOPN ( 1,
        TOPN ( 3,
            ADDCOLUMNS (
                SUMMARIZE ( Dim_Salespersons, [SalesId], [FullName] ),
                "Total Sales", CALCULATE ( SUM ( Fact_Transactions[Revenue] ) )
            ),
            [Total Sales], 0
        ),
        [Total Sales], 1
    ),
    Dim_Salespersons[FullName],
    ", "
)

Selected Salesperson by Place :=
CONCATENATEX (
    TOPN ( 1,
        TOPN ( [Selected Place],
            ADDCOLUMNS (
                SUMMARIZE ( Dim_Salespersons, [SalesId], [FullName] ),
                "Total Sales", CALCULATE ( SUM ( Fact_Transactions[Revenue] ) )
            ),
            [Total Sales], 0
        ),
        [Total Sales], 1
    ),
    Dim_Salespersons[FullName],
    ", "
)

Selected Place:=MIN ( Dim_Place[Place] )

Attachments:

You must be logged in to view attached files.

[Author]

Posts