Forum Discussion

ngomes's avatar
ngomes
Helper II
4 years ago
Solved

TOP with subgroups

I have a sales table matrix grouped by customer and article, this table is a TOP 2 sales
Does anyone know how I can show the total sales of all customers who are not part of the TOP 2 in a separate line from the top?

Table

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi ngomes ，

    Refer the following sample:

    First ,create rank column:

    rank = RANKX(FILTER('Table','Table'[TypeA]=EARLIER('Table'[TypeA])),'Table'[Value])

    Then create the below measure:

    test = IF(HASONEVALUE('Table'[TypeB]),MAX('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[TypeA]=MAX('Table'[TypeA])&&'Table'[rank]>2)))

    Create visual:

    You could adjust base on your table and need.

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi ngomes ，

    Refer the following sample:

    First ,create rank column:

    rank = RANKX(FILTER('Table','Table'[TypeA]=EARLIER('Table'[TypeA])),'Table'[Value])

    Then create the below measure:

    test = IF(HASONEVALUE('Table'[TypeB]),MAX('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[TypeA]=MAX('Table'[TypeA])&&'Table'[rank]>2)))

    Create visual:

    You could adjust base on your table and need.

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

    • ngomes's avatar
      ngomes
      Helper II
      4 years ago

      hello James

      I know the video
      This example does not have groups and what I am asking is how to obtain the value of the remaining customers knowing that I have the group customers articles/services