How to get Shop with most sales?

[Anonymous]

Hi there,

I'm trying to get the shop with the most sales in a Card visualisation. But I don't know how do this. 

The Sales column in this table is based on a measure.

So I want to have 'Berlijn' in the card.

[ninalee]

I'd like to launch something like Huffy, too (here are the huffy reviews from real clients, by the way). As for the solution of your problem, the previous commenter had already given the best advice as for me, I have nothing to add. 

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

I have done it in two ways.

1. Use the following formula to create measure:

Shop with the most sales =
MAXX ( FILTER ( 'Selling', [Sales] = MAXX ( 'Selling', [Sales] ) ), [Store] )

2. Use RANK() :

Use Rank =
CALCULATE (
    MAX ( 'Selling'[Store] ),
    FILTER ( 'Selling', RANKX ( ALL ( 'Selling' ), [Sales],, DESC, DENSE ) = 1 )
)

Here is the final outout:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

HI @Anonymous ,

You can created a calculated column as follows in Power BI:

flagCalc = 
IF(tablename[Sales] = MAX(tablename[Sales]), "YES", "NO")

Then create a card visual and move STORE column to it. Under visual filters for thid card visual, move the above calculated flagCalc column and set it to "YES".

This will give you BERLIJN in the card visual.

Thanks,

Pragati

[Anonymous]

I get this error: The MAX function only accepts a column reference as the argument number 1.

[Pragati11]

Hi @Anonymous ,

You need to create a calculated column. Are you creating a measure?

[Anonymous]

No , Modeling -> New Column

[Pragati11]

Hi @Anonymous ,

Can you share your DAX please? I think you are missing some brackets.

I tried creating similar column and don't get any error:

Thanks,

Pragati

[Anonymous]

My sales is a measure and not in column. 

max = IF(Verkoop[Omzet] = MAX([Omzet]), "yes", "no")

[Pragati11]

Hi @Anonymous ,

This flag needs to be a calculated column not a measure as I mentioned earlier.

[Anonymous]

You don't understand.. To calculate the sales.

Sales = sumx(

Selling,

Selling[Amount]*(Selling[Price]-Selling[discount])

)

[Pragati11]

HI @Anonymous ,

Apologies I missed that your SALES is a calculated measure.

You can create a Ranking column something like below:

rankCol = RANKX(ALL(tablename[Store]), [salesMeasure], , ASC, DENSE)

Now use this rankCol as a visual level filter in your card visual with STORE value in it and set it to display rankCol = 1

Thanks,

Pragati