cancel
Showing results for
Did you mean: Frequent Visitor

How to create a Table with a Group of all values lower than top x?

Hi guys,

I have a table loaded with 60+ records and I would like to create a table that exibihts only the Top 10 and a 11th item showing the total for all 50 remaining records. Is that possible using a DAX? Sorry if it is a dumb question but I started using PB on last week so...

Thanks!

1 ACCEPTED SOLUTION  Community Support

Hi @Acamara ,

DAX can do this but needs some preparatory steps first:

1. Add a new row name 'Remain records' in your initial table: 2. Add an index column in power query: After these steps, my sample data table will look like this: Now we can create a calculated table using the below dax formula:

test =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= 10
|| 'Table'[Record] = "Remain Records"
),
'Table'[Record],
"Result", IF (
[Record] <> "Remain Records",
[Record],
CALCULATE (
COUNT ( 'Table'[Record] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] > 10
&& 'Table'[Record] <> "Remain Records"
)
) & ""
)
)

Here is the result of the new table: Attached my sample file that hopes to help you if I understood your goal correctly: How to create a Table with a Group of all values lower than top x.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4  Community Support

Hi @Acamara ,

DAX can do this but needs some preparatory steps first:

1. Add a new row name 'Remain records' in your initial table: 2. Add an index column in power query: After these steps, my sample data table will look like this: Now we can create a calculated table using the below dax formula:

test =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= 10
|| 'Table'[Record] = "Remain Records"
),
'Table'[Record],
"Result", IF (
[Record] <> "Remain Records",
[Record],
CALCULATE (
COUNT ( 'Table'[Record] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] > 10
&& 'Table'[Record] <> "Remain Records"
)
) & ""
)
)

Here is the result of the new table: Attached my sample file that hopes to help you if I understood your goal correctly: How to create a Table with a Group of all values lower than top x.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly. Frequent Visitor

Worked.  Thank you!  Super User

Hi @Acamara ,

You can create this in Power Query using group by function.
Below are steps:
1) create duplicate of current table

2) select column by which you want to summarize and choose group by option. Under value, choose sum of column for measure
3) for new table, sort by total measure descending and index column starting from 1

4) add conditional column "Criteria", if index <= 10 then index column else "Other"

5) group by columns Consumer_Id and Criteria

5) create new conditional column "Final text", if criteria <> "Other" then Consumer_Id, else "Other"

6) group by column "Final text"

Below are screenshots from my demo:     Cheers,
Nemanja  Super User

This is doable but tricky.  One way would require addition of another table with values 1-10 and "Other" to be used as the rows on your table.  It probably wouldn't be that flexible toward changes or adapting later if you are just starting out.  I would encourage you to make a table that shows the top 10, and then a card that you placed below the table with a Measure called "Other" that give the sum of the not-Top 10.  If that works, the community can provide example DAX.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!  