TOPN - Getting inconsistent results

Question

I am using the AdventureWorks2016 database and I created a simple table showing all products with their total sales. The sales amount are correct.

The sales amount values come from a measure named [Sum of Internet Sales]. that looks like this:

Sum of Internet Sales = 
    SUM('Internet Sales'[Sales Amount])

There is a 1:M relationship between 'Product' and 'Internet Sales'.

In DAX Studio, I created the following query to get the top value:

SELECTCOLUMNS(
    TOPN(
        1,
        'Product',
        [Sum of Internet Sales],
        DESC
        ),
    "Product Name",
    'Product'[Product Name]
    )

The query returns Road-150 Red, 48. This clearly isn't correct.

What's even more strange is if I change TOPN to return the top 2 values instead of 1, I get this:

Road-150 Red, 62
Road-150 Red, 48

Finally, if I change it to return the top 10, I get this:

Mountain-200 Silver, 46
Mountain-200 Silver, 38
Mountain-200 Black, 38
Road-150 Red, 44
Road-150 Red, 62
Mountain-200 Black, 46
Mountain-200 Black, 42
Road-150 Red, 52
Road-150 Red, 56
Road-150 Red, 48

I thought I understood TOPN until I ran into this. Or, perhaps my simple measure isn't correct?

Very strange indeed, what do you get when you try something like: topn(1, values('Product'[Product Name]), [Sum Of Internet Sales] )? — StelioK
Makes no sense.. is the data embedded in the .pbix? Or are you using AAS/SSAS? — StelioK

greggyb greggyb · Accepted Answer · 2019-09-11T21:04:03

Actually just grabbed AdventureWorks and @StelioK was spot on in the first comment.

'Product' in AdventureWorks is a slowly changing dimension. There are multiple distinct rows for some products which share the same name. Since you are referencing the whole table in your TOPN, the value of [Sum of Internet Sales] is the sum for a specific version of a product. Sales for "Mountain-200 Black, 46" are split across several 'Product'[ProductKey]s:

EVALUATE
SUMMARIZECOLUMNS(
    'Product'[ProductKey],
    'Product'[EnglishProductName],
    TREATAS ( {"Mountain-200 Black, 46"}, 'Product'[EnglishProductName] ),
    "Sales", [Sum of Internet Sales]
)

/* results in:
| ProductKey | EnglishProductName     |       Sales |
|------------+------------------------+-------------|
|        362 | Mountain-200 Black, 46 | 411868.7382 |
|        363 | Mountain-200 Black, 46 |   961600.81 |
*/

Whereas sales for the top product (not the top 'Product'[Product Name], but the row in 'Product' associated with the highest sales) is shown below:

EVALUATE
SUMMARIZECOLUMNS(
    'Product'[ProductKey],
    'Product'[EnglishProductName],
    TREATAS ( {"Road-150 Red, 48"}, 'Product'[EnglishProductName] ),
    "Sales", [Sum of Internet Sales]
)

/* results in:
| ProductKey | EnglishProductName |      Sales |
|------------+--------------------+------------|
|        312 | Road-150 Red, 48   | 1205876.99 |
*/

As you can see sales for the row in 'Product' with [ProductKey]=312 are, in fact, higher than sales for either of [ProductKey]=362 or [ProductKey]=363.

TOPN - Getting inconsistent results

1 Answers