# DAX show Best selling product only



## martijnvanderveldt (Oct 13, 2015)

Hii!   

I am looking for a DAX formula that only shows me the best selling product per region. Thus if region 1 has 3 brands in which brand A is the best selling brand, I would like to use the formula to create a table in which I see:

Region 1: Brand A (Volume included optional, not necessary) 


Anyone any idea? (Columns: Brand, Volume, Region)


----------



## scottsen (Oct 14, 2015)

=CALCULATE([SomeMeasure], TOPN(1, SomeTable, [SomeMeasure]))

I _think_ that should magically work to show just the best (whatever)... as ordered by [SomeMeasure] ?


----------



## martijnvanderveldt (Oct 14, 2015)

Awesome we are getting a lot closer, though it stills gives an error when I do this. Maybe I am doing something wrong?

I made the following formula as a measure:

TOP = CALCULATE(TOPN(1,Volume,Volume[SKU]))
or
TOP = CALCULATE(TOPN(1,Volume,Volume[SKU],ASC),ALLSELECTED(Volume))

But it gives me the following error:
_The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value_.

Would you know what I should do to make it work? Here Volume is the table and volume[SKU] a column


----------



## scottsen (Oct 14, 2015)

So... how to explain this... uh...

All measures must return a scalar value (a simple, single value).  TOPN does NOT return a scalar, it returns a TABLE.  

Assuming Volume[SKU] is a column... maybe...

TOP = CALCULATE(SUM(Volume[SKU]), TOPN1(1, Volume, SUM(Volume[SKU]))

The first param to calculate is a measure, the 2nd (or more) params to calculate are "how do I want to modify the filter context" -- in this case you want to only include rows from a table you passed in.  That table you passed in... is the results froma  TOPN(1, ...)

Does that make sense?


----------



## martijnvanderveldt (Oct 16, 2015)

Hmm it does make sense yet it doesn't returns what I am looking for. Or maybe I am simply using it wrong in a table thereafter. 

I have the table  'Volume' with the following columns:

      Volume[SKU]                   (Text)
      Volume[Volume (HL)]       (Values)

In which I tried your formula 

Top = CALCULATE(SUM(Volume[Volume (HL)]),TOPN(1,Volume,SUM(Volume[Volume (HL)])

Yet I believe it is missing the attaching column of Volume[SKU]. When I try to add the measure in a table with the SKU's separate it just gives me a list of all SKU's and not just the top selling one which I am looking for. Would you know how to make that magic happen?

To Clarify, from the 100 brands, I only want 1 to show up as the best (depending on the filters I use).

Thanks a lot for your help! Really appreciate it


----------

