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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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