Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello I would like to get the top 10 of my collected commissions, but I also want to prevent the number 1 from appearing, since it is not identified.
The formula I use is:
M_Top10 =
VAR ComPorTipo =
SUMMARIZE (
COMT04,
COMT04 [C76CODCOM],
"TotCom", SUM (COMT04 [C76IMPRE01]) )
VAR SumFiltered =
TOPN(
10,
ComPorTipo,
[TotCom]
)
RETURN SumFiltered
However it gives error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
I will appreciate your help for these two queries.
Thank you very much
@Guille2711410 , Try like
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
This is an expression that returns a 10-row table, so it will error in a measure expression. To get the list of your Top 10, you can use CONCATENATEX, but I am not sure if that is what you need. You can change the Return in your expression to the following.
Return CONCATENATEX(SumFiltered, COMT04 [C76CODCOM])
If you want to omit the top 1, you can do a TOPN(9, SumFiltered, [TotCom], ASC), and then do CONCATENATEX on that table.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |