Attempting to do a Top 10 / Other customer list

[slukas]

First, let me just say that a am very new to BI.  I can barely spell DAX.  So, now the issue.

I found an example posted on a German web site that used the AdventureWorks sample data base to demonstrate Top n / Other.  I have it working - mostly.  The issue is the amount calculated for "Other".  It does not account for the amounts shown for the Top n products.  The total sales for all products is around 29.36m.   Running the display and selecting a Top 10 products returns around 13.5m for the Top 10 products.  That amount should be removed for the group Other but it is not.  I am hoping that someone can take a look at the DAX statement below and determine what is wrong with it.  The web site is here - Top n and Other.

To simplify this I have extracted the code and placed it next.  Be aware that the web page had a few mistakes.  The most common issue is that semi-colons have to be replaced with commas.

Other = 
    VAR TopNumber = IF ( HASONEVALUE ( 'TopN Filter'[TopN] ), VALUES ( 'TopN Filter'[TopN] ), 10 )
    VAR Rest      = COUNTROWS ( DimProduct ) - TopNumber
         RETURN IF ( HASONEVALUE ( ProductName[Product Name] ),
                     IF ( VALUES ( ProductName[Product Name] ) = "Others",
                          SUMX ( TOPN ( Rest, ADDCOLUMNS ( VALUES ( DimProduct[EnglishProductName] ),
                                                                    "Measure", [Sum of Sales Amount] ),
                                                                    [Measure], ASC ), [Measure]
                               )
                        )
                   )

Thanks,

Steve

[slukas · Accepted solution]

OK.  I figured out the problem which is the way I learn best.  The problem did exist in the statement I listed.  Specifically this piece of DAX

VAR Rest = COUNTROWS ( DimProduct ) - TopNumber

had to be corrected to this

VAR Rest = DISTINCTCOUNT ( DimProduct[ProductName] ) - TopValue

This provided the correct number of rows that needed to be totaled for "Other"