Name of Top 1,2,3 Stores within a tooltip

[MarkCBB]

Hi There,

I am looking to create a DAX Measure that results in the Name of the top performing Store, 2nd Store and 3rd Store within the current filter context. so there will be 3 item in the tool tip pop up. I am just looking for the Name of the store.

My database is set up in a standard Star layout with the following tables:

Fact

Stores

Products

Calendar

The measure that will determine the Top 1,2,3 store is based on the Units sales. 

I Goal is for the user to be able to hover over a Channel (many stores are within a Channel) and the tooltip will tell them the top 1,2 and 3rd Store based on the performance of Unit sales. 

here is a sample set of the data: 

DATA

Regards,

Mark B

[danextian · Accepted solution]

hi @MarkCBB,

I'm not sure which among the fields in your dataset pertains to the store so I just assumed it is PLACE ID.

I created the following measures:

sum of units = 
SUM ( 'FACT'[UNITS] )

store rank =
RANKX ( ALL ( tblCHANNEL[PLACE ID] ), [sum of units] )

I am not sure how you want to displace your top 3 in the hover so I just created two top3 measures to be used as tooltips:

top 3 =
CALCULATE (
    CONCATENATEX (
        VALUES ( tblCHANNEL[PLACE ID] ),
        tblCHANNEL[PLACE ID],
        ", " & UNICHAR ( 10 )
    ),
    FILTER ( ALL ( tblCHANNEL[PLACE ID] ), [store rank] <= 3 )
)

Top 3 (w/ order) =
VAR top1 =
    "Top 1: "
        & FILTER ( VALUES ( tblCHANNEL[PLACE ID] ), [store rank] = 1 )
        & ", "
        & UNICHAR ( 10 )
VAR top2 =
    "Top 2: "
        & FILTER ( VALUES ( tblCHANNEL[PLACE ID] ), [store rank] = 2 )
        & ", "
        & UNICHAR ( 10 )
VAR top3 =
    "Top 3: "
        & FILTER ( VALUES ( tblCHANNEL[PLACE ID] ), [store rank] = 1 )
RETURN
    top1 & top2
        & top3

I added UNICHAR(10) just in case you want to show them seperated by line breaks on a matrix (you need to turn on word wrap for value).

And here's one more measure if you want to show the total value of those top 3 stores:

sum of units (top 3) = 
CALCULATE (
    [sum of units],
    FILTER ( ALL ( tblCHANNEL[PLACE ID] ), [store rank] <= 3 )
)

The tooltip measures shoud look like this:

Please don't forget to kudos or accept this post as solution if you find this helpful. 🙂

[MarkCBB]

@danextian that is exactly what I was looking for, thank you for he help.