Grouping data for in a chart

[0xygen27]

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.

[v-huizhn-msft · Accepted 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

[v-huizhn-msft · Accepted 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

[0xygen27]

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 🙂

[v-huizhn-msft · Accepted solution]

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

[0xygen27]

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 🙂