Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

 

Worked.  Thank you!

nandic
Memorable Member
Memorable Member

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.