The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 🙂
User | Count |
---|---|
80 | |
74 | |
41 | |
30 | |
28 |
User | Count |
---|---|
107 | |
96 | |
53 | |
47 | |
47 |