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.
Dear Community,
I want to group data from 1 column in other columns.
I got a table with:
Number Visits Percentage
1 50 18,38%
2 40 14,70%
3 30 0,11%
4 29 %
5 27 %
6 26 %
7 25 %
8 20 %
9 15 %
10 10 %
I want to have a column where I can show the top percentage of the top 3, top 5, top 10, top 50, top 100 etc.
What is a DAX formula which I can use for this?
Greetings,
0xygen27.
Solved! Go to Solution.
Hi @0xygen27,
Please create a calculated column using the formula and get the percentage for each value.
percentage = Table1[ Visits]/CALCULATE(SUM(Table1[ Visits]),ALL(Table1))
You can create a new table to get percentage of the top 3, top 5, top 10, top 50, top 100 etc. For example, I create a new table used to display the top 5 rows.
Table = TOPN(5,Table1,Table1[percentage])
You want to create a column in original table or create a visual? I am still confused with it, please share more details.
Best Regards,
Angelia
Hi @0xygen27,
You can create a calculated column used to order the percentage descending, then use the rank column to select lastn rows. I still using the sample table given above.
rank = RANKX(Table1,Table1[percentage],,DESC)
Then create a new table using the formula below and get expected result.
Table = TOPN(5,Table1,Table1[rank])
Best Regards,
Angelia
Hi @0xygen27,
Please create a calculated column using the formula and get the percentage for each value.
percentage = Table1[ Visits]/CALCULATE(SUM(Table1[ Visits]),ALL(Table1))
You can create a new table to get percentage of the top 3, top 5, top 10, top 50, top 100 etc. For example, I create a new table used to display the top 5 rows.
Table = TOPN(5,Table1,Table1[percentage])
You want to create a column in original table or create a visual? I am still confused with it, please share more details.
Best Regards,
Angelia
I forgot to mention I already had the percentages, so I only had to make new tables with the topn function. Thanks alot. Is there also a function that shows the last X rows? So not topn but lastn?
Thanks for your help already really appreciate it 🙂
Hi @0xygen27,
You can create a calculated column used to order the percentage descending, then use the rank column to select lastn rows. I still using the sample table given above.
rank = RANKX(Table1,Table1[percentage],,DESC)
Then create a new table using the formula below and get expected result.
Table = TOPN(5,Table1,Table1[rank])
Best Regards,
Angelia
Dear angelia,
Thanks for you help and clear instructions. You helped me alot, not only with the code but also witht the way of thinking. If you can't just summarize the last 10 rows then place the rows up top and fix the problem :D. Really helpfull 🙂
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |