DAX show Best selling product only

[martijnvanderveldt]

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]

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

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

 

[martijnvanderveldt]

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]

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]

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