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 original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

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.

1. Bryan Nice says:

It would be nice if you posted for the even case too

3. David Hager says:

=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. powerpivotpro says:

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.

2. David Hager says:

Just use =RAND()-0.5, as stated above, for same effect.

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. sorry, in MS Access there is not RANDBETWEEN, only Rnd