Welcome back P3 Adaptive nation, today I’d like to demonstrate some exciting ways to show Top Performing “things” using DAX. Most of what we’ll discuss has been covered before in previous posts written here at P3 Adaptive . Rob has extensively covered the topic of Top Performing “things” in not one, not two, but THREE previous posts! So today I want to start us off by reviewing these awesome techniques, and taking a look at how to some of those practices can be used in a Power BI report. 
Previous Top Performing Posts:
- Find The Top Selling Date, Product, etc…
- Top-Selling Product Using FIRSTNONBLANK()
- Another Way To Find The Top Performing “Thing”
DAX Can Return More Than Just Numbers Or Dates…
The beautiful thing about Top Performing DAX Measures is that it’s portable & dynamic!
Basically any way we slice and dice our data, [Top Sales Day] will change with it, JUST like any other DAX Measure (E.g. [Total Orders] or [Total Profits]). In my opinion, showing top performers is a powerful (and useful!) DAX Measure. I’m not sure about you, but when I was first learning DAX years back, I never considered using a “Math Engine” to return text outputs. Rob’s post really helped open my eyes to the world of possibilities that DAX is capable of.
Today’s post will be using the SUMMARIZE method to return my Top Performing “Thing”. Rob’s second post originally used FIRSTNONBLANK() to return the same result. However, Gerhard Brueckl provided a great follow up in the post comments showcasing a FASTER way to do this using SUMMARIZE. The beauty in using SUMMARIZE is that it allows us to avoid using the expensive FILTER function, which is performance hungry. Instead TOPN can filter the table from SUMMARIZE using row-context (E.g. return only the first row with the top [DAX Measure]). Using the image above as the example, let’s take a look at the [Top Sales Day] measure that uses the SUMMARIZE function.
Top Sales Day =
IF (
NOT ( ISBLANK ( [Total Orders] ) ),
MAXX (
TOPN (
1,
SUMMARIZE ( ‘Calendar’, Calendar[Date], “Order Count”, [Total Orders] ),
[Order Count]
),
‘Calendar'[Date]
)
)
As you can see there’s multiple layers to this DAX Measure, but I can assure you they all have a purpose!
Let’s start from the inner most query and move our way out. 1) The SUMMARIZE function returns a virtual summary table of Sales Days and [Total Orders]. 2) We then use TOPN to return the row (Sales Day) with the highest order count from the SUMMARIZE virtual table. However TOPN returns a table, and DAX measures can’t display a table…they require a single (scalar) value output. 3) X Functions to the rescue! Since we need a single value output we can use the MAXX to convert our table INTO a single (scalar) value, which for us is the Sales Day. To be fair I’m arbitrarily using the MAX(X) function, but I could have just as easily used ANY of the X Functions (E.g. MINX, SUMX, etc…). The only purpose of this step is to convert a table to a single (scalar) value output. Simple right? 
Why Stop Here? Let’s Return The Top Value Too!
Not only can you use DAX to return a Top Performing “thing”, we can also return the Top Value! Rob’s most recent post on top performers shows an easy way to return the value associated with the top performing thing. Let’s say we had a scenario where we ALSO wanted to know what the Top Sales Day Amount was. We could use a variation of the above DAX Measure, this is true. However what if I also wanted to know the DOLLAR AMOUNT associated with that sales day? Well friends, we can have our cake and eat it too in this scenario. We ask, and DAX can deliver!
Top Sales Day Amount =
MAXX(‘Calendar’, [Total Sales])
Compared to our Top Purchasing Customer Measure, this one is much simpler! The measure looks at our calendar table, and then returns the value [Total Sales] for the row (which is day on the calendar table). With our amount measure on hand, we can create an easy DAX measure that uses CONCATENATE to bring together both of these values. Throw in a little formatting love to make our values display correctly, and we have ourselves a magical formula! I’d highly recommend giving Rob’s third article a read for more information on this one.
Top Sales Amount & Day =
FORMAT ( [Top Sales Day Amount], “$0,0″ ) & ” / “
& FORMAT ( [Top Sales Day], “mmm dd, yyyy” )
All of these DAX Measures are available in the embedded Power BI Report or in the report download link below. For anyone wanting to know how I made my DAX measures as pretty as they are, checkout a post I wrote on an awesome tool called DAX Formatter. That’s it for today’s post, I hope you enjoyed today’s post as much as I enjoyed making it! As long as you were able to add another tool (or two) to your belt, then my job is done. As always I look forward to reading your comments, until next time!
Get Your Files
Get in touch with a P3 team member