skip to Main Content

Well folks I haven’t had much time to play with the V2 Beta (aka CTP3) yet.  A lot going on over the past week:

  1. Authoring a brand new suite of models and reports for our Duane Reade DR-Direct solution
  2. A nasty crashing bug in PowerPivot for SharePoint that we’ve now learned to catch and repair
  3. I submitted a PowerPivot article to CIMA Insight magazine (for August)
  4. Running refresh tests with PowerPivot servers in Chicago pulling data from SQL in Dallas (over Internet VPN – more on this later) for the HostedPowerPivot offering

So in the meantime, David Hager has a short article to share on the new TOPN function.  I haven’t had a chance to try it yet, and I’m a little surprised that it doesn’t have special handling for ties (as he’s discovered).

David Hager on TOPN

Using the New PowerPivot Version 2 TOPN Function: Up, Down and Between

One of the new DAX functions included in the Denali PowerPivot CTP3 release is the TOPN function. This function returns a table which can be aggregated for the top n values. Prior to the release of this function, workarounds were available to achieve the same results, mainly through DAX ranking formulas (and Denali PowerPivot now has native functions for ranking too!). So, a typical DAX formula which uses the TOPN function is:

=SUMX(TOPN(N,SalesTable,SalesTable[SalesTotal]),SalesTable[SalesTotal])

where SalesTable is the table and SalesTotal is the column in that table containing the top n results to be analyzed.

Well, that was easy enough. However, there is a systemic problem here and it’s mainly data-related. In order to understand why the TOPN function don’t work the way you might expect, read the following that comes from the Denali PowerPivot CTP3 help documentation on TOPN.

“If there is a tie, in order_by values, at the Nth row of the table, then all tied rows are returned. Then, when there are ties at the Nth row the function might return more than n rows.”

In English, that means if there are duplicate values in your data, the TOPN function will return the WRONG answer if the Nth value is adjacent to duplicates. In order to get a meaningful result then, the data must be converted so that each data point has a unique value. The question is, how do you do that and retain the integrity of the data? An Excel trick can be used here as a workaround. This method requires that each value be incremented by a very small but unique value. The best implementation of this comes in the form of a calculated column with the following formula

SalesTable[UniqueIncremValue] = 
IF(RANDBETWEEN(-1,1)=-1,-RAND()*2,RAND())*0.000001

The goal of this formula is to create a column of unique numbers add up basically to zero. There is a balance that is required in this formula between affecting data and insuring that all values in the dataset are unique. The RAND function returns a 15-place decimal, which is the limit in Excel. However, if all of the decimal places were used, the value of the number could be as high as 1 or as low as -2. By multiplying the random numbers by a very small number, the numbers will not interfere with the individual values if they themselves are not small numbers. However, if the multiplier is made too small, not all of the random numbers may be unique depending on the size of the dataset. So, if you are going to use this workaround, you need to test [UniqueIncremValue] to see if all of the values are unique. The best way to do this is to compare its distinct count with the distinct count of a primary key column in the table. If they are equal, TOPN will work for any value of N.

Now, the original formula will work fine if the new calculated column is used in place of SalesTable[SalesTotal].

SalesTable[SalesTotalMod]=
SalesTable[SalesTotal]+ SalesTable[UniqueIncremValue]

It should also be noted that the TOPN function can used in a DAX like the one shown below to afford a BOTTONM result. Note that this formula takes advantage of the new DISTICTCOUNT function.

=SUMX(SalesTable,[SalesTotalMod])
-SUMX(
     TOPN(
         DISTINCTCOUNT(SalesTable[SalesTotalMod])- N,
         SalesTable,
         SalesTable[SalesTotalMod]
         ),
     SalesTable[SalesTotalMod]
    
)

I think you can see that the TOPN function is a valuable new item for your DAX toolbox. Have fun!

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 10 Comments
  1. Would have been better if Msft gave us an extra parameter for TOPN, which resolves the ties problem natively, but I guess we’ll have to live with this workaround for the near future.

  2. DAX formula for median (odd # of items in column) using TOPN.

    =SUMX(TOPN(ROUND(COUNT([SalesOrderMod])/2,0)+1,SalesTable,[SalesOrderMod]),[SalesOrderMod])-SUMX(TOPN(ROUND(COUNT([SalesOrderMod])/2,0),SalesTable,[SalesOrderMod]),[SalesOrderMod])

    Formula for even # is larger, but uses same methodology.

  3. =RAND()-0.5 works just as good as the formula in this article to generate a column of random numbers that add up essentially to zero.

  4. Hi,
    you teach us we should avoid calculation in PowerPivot where possible. Is it a chance to do something like RANDBETWEEN in MS Access and import only 1 final value field in PowerPivot?

    thank you!

    1. It is true that imported columns generally compress better AND lead to faster slicer click performance than calculated columns in PowerPivot. That said, I don’t really understand your question yet, specifically why RANDBETWEEN would be useful.

  5. in access i have 1.5 mln rows with sales data and there is the ties problem. I can avoid this problem in PowerPivot with a formula from this post
    …SalesTable[UniqueIncremValue] = IF(RANDBETWEEN(-1,1)=-1,-RAND()*2,RAND())*0.000001
    my question is can I do something like this in access and import in PowerPivot just sum of sales data and UniqueIncremValue

    Thank you!

  6. Hi All ,

    I Have a requirement say used TDIST Function in Excel (=IFERROR(TDIST(ABS((E8/100))/SQRT(($AU8/$I8)+($AX8/$B8)),($B8+$I8-2),2), 0))

    Want To use same TDIST Function IN Pivot DAX Model Can any one please let me know Syntax for TDIST in DAX

Leave a Comment or Question