cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Acamara
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
v-yingjl
Community Support
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:

records.png

2. Add an index column in power query:

index column.png

After these steps, my sample data table will look like this:

record index.png

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:

record result.png

 

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.

 

 

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
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:

records.png

2. Add an index column in power query:

index column.png

After these steps, my sample data table will look like this:

record index.png

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:

record result.png

 

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.

 

 

View solution in original post

Acamara
Frequent Visitor

Worked.  Thank you!

nandic
Super User
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:

top n 1.PNGtop n 2.PNGtop n 4.PNGtop n 5.PNGtop n 6.PNG

Cheers,
Nemanja

mahoneypat
Super User
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors