skip to Main Content

Welcome back P3 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 PowerPivotPro. 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. Smile

Previous Top Performing Posts:

DAX Can Return More Than Just Numbers Or Dates…

DAX Top Sales Day Using SUMMARIZEThe 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]     )
)

DAX Top Sales Day & Amount Using SUMMARIZEAs 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? Winking smile

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!

Download the Power BI Desktop (.pbix) Report Here

X

Get Your Files

Reid Havens

Reid Havens is the Founder & CEO of Havens Consulting Inc. He has a formal background in technology and organizational management, having obtained separate degrees in Organizational Development & Business Analytics. Reid has experience working with Fortune 500 companies such as Microsoft as well as with Non-Profit Organizations. He is also the Executive Editor, contributing author, and Principal Consultant at PowerPivotPro. Additionally he's an instructor at the University of Washington and other universities, teaching Business Intelligence, Reporting, & Data Visualization.

This Post Has 12 Comments
  1. Hi Reid, Gerhard Brueckl’s answer goes back to 2012 when SUMMARIZE wasn’t well understood. Wouldn’t this similar DAX expression be faster?

    Top Sales Day=
    IF (
    NOT ( ISBLANK ( [Total Orders] ) ),
    MAXX (
    TOPN (
    1,
    ADDCOLUMNS ( VALUES ( Calendar[Date] ), “Order Count”, [Total Orders] ),
    [Order Count]
    ),
    ‘Calendar'[Date]
    )
    )

    or this using the DAX 2016 SUMMARIZECOLUMNS function:

    Top Sales Day=
    IF (
    NOT ( ISBLANK ( [Total Orders] ) ),
    MAXX (
    TOPN (
    1,
    SUMMARIZECOLUMNS ( Calendar[Date], “Order Count”, [Total Orders] ),
    [Order Count]
    ),
    ‘Calendar'[Date]
    )
    )

    best regards

    1. Great catch. The SUMMARIZECOLUMNS (wasn’t available when the original was written) would work a little bit better. I was so busy thinking of restructuring the old post into Power BI, I completely overlooked that function. Much appreciated for providing that in the comments!

      1. The ADDCOLUMNS version works and is likely a bit faster. Sadly, however, at this time SUMMARIZECOLUMNS is not allowed to be used in a modified filter context (in this case each Sales Person affects the filter context), so if you try to use that measure in a visualization you’ll get an error . It’s too bad because I’ve wanted to use SUMMARIZECOLUMNS for the purported speed gains, but in most cases have been unable to.

        1. @Narayana, @Reid: Yes you’re right SUMMARIZECOLUMNS doesn’t work inside a row context . I forgot about that even though Alberto and Marco wrote an excellent article on the subject.

          http://www.sqlbi.com/articles/introducing-summarizecolumns/

          It’s my impression that SUMMARIZECOLUMNS originally was commissioned by the Power BI team to enable them in a more efficient way to add Pivot Table like features. Put in another way the function started out as a client tool helper and is now gradually making its way into working like an ordinary DAX function. I don’t know if you agree with that?

          1. I definitely think it was originally designed more for development, and investigation of the data. I use it a lot when I’m breaking apart a DAX query to see the underlying table data. I’ve also found it incredibly useful if I’m ever needing to download a table into a custom data model for a client, pulling from a tabular cube. I often build out my DAX SUMMARIZECOLUMNS query in DAX Studio, then copy it over into Power Query for import to my data model.

  2. Hi any suggestions how we could get the BOTTOM sales date (= date on which we sold the least) ,… it seems we get stuck with the dates that have no sales (like weekends, ;;) we should be able to exclude these to get the correct date

    1. Hi there, there’s a DAX Function called WEEKNUM which coverts a date to 1-7. Use that in a CALCULATE Filter Context to remove the 1’s and 7’s from looking at the bottom sales date, it’ll prevent a weekend from showing up.

      1. Tx for the suggestion although this will not work in most cases … the weekends were just an example where we could have no data…but if we would have a date in the week (public holidays or so) without data …this workaround would not work neither… Any other suggestions ?

        1. Two thoughts: 1.) Create a column in your calendar table to identify business days versus weekdays or holidays or 2.) Create a summary table of dates where sales > 0 and include only those dates

Leave a Comment or Question