Struggling with Topn and/or Cross filtering

DAX DAX Calculations
1

I have a report page with a slicer on sales exec and lots of performance charts. All good.
I have used the ALL function to get the team amounts against his. Just fantastic.

My problem is I’d like a card showing the top performer in the team. I’ve tried:

  1. Topn with Top1 (having watched Sam’s videos) but they just identifty the amounts and it’s the name of the top sales exec I need in the card. So how do we get the Top sales exec name onto the card.
  2. I also tried creating a small table to try and id top sales exec instead of a card but it just shows the filtered sales exec even when I hit the non button on the sales exec slicer??

Any ideas here?
Pete

2

@Pete673,

Try this:

  1. Set up your TOPN(1) calculation as a variable within a measure. Assume the VAR is called vTable.
  2. Return the following structure:

 CALCULATE
     MAXX(
         vTable,
        [Salesperson Name]
     )
 )

I’ll try to find an example on the forum where I’ve done this - I know I use this approach frequently.

Hope that’s helpful.

  • Brian

P.S. Found a recent measure I wrote that used this same general construct. It’s got some additional virtual table manipulations, but the relevant portion is in the red box:

image
image454×587 10.7 KB

Note that since vTable10thItem is a one row table (due to TOPN(1) ), you could use almost any X function (e.g., MINX, AVERAGEX, MAXX, etc). The key thing is that you can’t use a simple columnar function (such as MAX), since those have no way of referencing a virtual table.

3

Hello @Pete673,

Thank you for posting the query on to the Forum.

This type of similar query was asked onto the forum and @sam.mckay had addressed this query.

Also he has prepared a video on this.

I’m providing the links of the video and also of the query which was posted onto the Forum for the reference.

Hoping you’ll find this useful and can help you to achieve your desired analysis.

Please feel free to write back in case I’ve missed out on anything.

Thanks and Warm Regards,
Harsh

[Query Posted on the Forum] -
(Product Name as Measure for Highest Sales Product)

[Video of Showing Top Product in Card Visual] -

4

Thanks Harsh and Brian. All working.
Not only that I’ve got a pattern for future dev

Cheers
Pete

5

You’re welcome @Pete673,

Glad we were able to help you.

Thanks & Warm Regards,
Harsh

6

It’s great to know that you are making progress with your query @Pete673. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!