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
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
Solved! Go to 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.
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!
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.
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!
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?
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |