Showing Top customers in a Table using TOPN

[CupidC]

Hi all
I was trying to use TOPN function to show the Top 3 customers in a Table visual, but I couldn't figure it out what is wrong with my formula. any advice would be great 😞

Original Table:

A	500
A	300
A	500
B	700
B	200
C	323
C	500
C	300
D	100
D	700
D	300
E	220
E	565
E	667
F	839
F	122

My TopN Formula:

Top 3 customer =

VAR topcstuomer = topn(3,VALUES('Sales'[Name]),[Total sales],DESC)

Return

CALCULATE([Total sales],topcstuomer)

Result:

The result showing all customers instead of the top 3, but the total is correct - showing the sum of top3

Result that I hope for:

[VahidDM · Accepted solution]

Hi @CupidC 

You can just add a simple filter on that visual like this to find those Top 3 items:

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Total = SUM(Data[Sales])

Measure = CALCULATE([Total],topn(3,ALL(Data[Customer]),[Total]),VALUES(Data[Customer]))

Hope this helps.

[CupidC]

HI Ashish

Thanks. could you explain what "VALUES(Data[Customer])" does at the end..

It works, but not sure why it works......

Thanks

Cupid

[Ashish_Mathur]

Hi,

The VALUES() function returns a unique list of all the Customers.  So while the TOPN function returns the top 3 from among all customers, the return result in the visual just has 3 customers. 

[VahidDM · Accepted solution]

Hi @CupidC 

You can just add a simple filter on that visual like this to find those Top 3 items:

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/