Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Luis98
Resolver II
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
ERD
Super User
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] )

 

ERD_0-1668622184067.png

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.

ERD_0-1668622695034.png

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!

View solution in original post

6 REPLIES 6
ERD
Super User
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] )

 

ERD_0-1668622184067.png

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.

ERD_0-1668622695034.png

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!

Thanks @ERD !

 

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

 

Jayee
Responsive Resident
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!!

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
Responsive Resident
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.

Mmmm no, i have just one top 1.

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors