Forum Discussion

0xygen27's avatar
0xygen27
Advocate II
9 years ago
Solved

Grouping data for in a chart

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%          ...
Need Help
Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
0xygen27
Advocate II
9 years ago

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 :)