cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver II

## Get the Value of TOPN sales by customer

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

1 ACCEPTED SOLUTION
Super User

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/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

6 REPLIES 6
Super User

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/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Resolver II

Thanks @ERD !

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

Responsive Resident

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!!

Resolver II

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?

Responsive Resident

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.

Resolver II

Mmmm no, i have just one top 1.

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors