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%                

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.

 

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

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    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

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

      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'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