Get the Value of TOPN sales by customer

[Luis98]

Hi,

I dont know if this is exactly solved it in other post, i tried to find out but i didnt have luck.

I have the next table:

Customer		Amount
Customer1		5
Customer2		7
Customer3		10
Customer1		5
Customer4		2
Customer3		3
Customer2		4
Customer1		5
Customer5		1

I want obtain in a visual card 15, that would be the total of the Customer1, who is the customer with more amount.

I hope you can help me

[ERD · Accepted solution]

Hello @Luis98 ,

One of the ways to achieve the result:

Create a measure:

_Amount = SUM ( Customers[Amount] )

Second measure:

TOPCustomerValue = MAXX ( VALUES ( Customers[Customer] ), [_Amount] )

Another way with TOPN (can be used both to retreive the TOP value or the TOP Customer name):

TOPCustomer =
VAR topCustomer =
    TOPN (
        1,
        ADDCOLUMNS ( VALUES ( Customers[Customer] ), "@Amount per customer", [_Amount] ),
        [@Amount per customer]
    )
RETURN
    MAXX ( topCustomer, [Customer] )

In the example above we retreive the  TOP Customer name.

Please, take into account that TOPN returns an entire table or a table with one or more columns. That is why you had errors before.

https://dax.guide/topn/

[Luis98]

Thanks @ERD !

This is exactly what I need, it works perfectly. 😁

[Jayee]

Hi @Luis98 ,

try below measure

Measure = TOPN (
1,
ADDCOLUMNS (
VALUES ( 'Table'[Customer] ),
"@Amount", [Amount]
),
[@Amount],
DESC
)

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!

[Luis98]

No, with this Measure i get this error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value",

i tried using [Amount] as column and as measure which SUM([Amount]), but neither work.

Some idea?

[Jayee]

May be you can try to check with table visual in filter pane you have the option to do Top N on customer filter add amount and filter with 1 top n and see as power bi is filters based on value if the value is same for multiple customers it will show all of them.

[Luis98]

Mmmm no, i have just one top 1.

Why are you calling "@Amount" and [@Amount]? I lose myself in this step