topic Re: Display first 10 values as is and remaining row values with specific name in DAX Commands and Tips

Display first 10 values as is and remaining row values with specific name

DAXUser — Thu, 19 Jan 2023 13:57:45 GMT

Hi ,

There are many rows in my table. The Category name should be displayed in its current form for the first 10 rows. For the remaining records, starting with row 11, the Category should be displayed as "Others," and the remaining total values must be summed. The report connected with Live Connection

Category	Total
a	30
b	40
c	50
d	60
e	70
f	80
g	90
h	100
i	110
j	120
k	130
l	140
m	150
n	160

Expected Output

Category	Total
a	30
b	40
c	50
d	60
e	70
f	80
g	90
h	100
i	110
j	120
others	580

Thanks

Re: Display first 10 values as is and remaining row values with specific name

TomasAndersson — Thu, 19 Jan 2023 14:08:28 GMT

Hi!
Check out this guide: Display Top N items and Others in Power BI - Goodly

You will have to modify it according to your use case. For example, define what columns defines the sorting order, i.e. what will be the top 10 and what will be Other.

Since you know that you want top 10, you don't have to bother about implementing the dynamic top N slicer.

Good luck!

Re: Display first 10 values as is and remaining row values with specific name

FreemanZ — Thu, 19 Jan 2023 14:46:41 GMT

hi DAXUser

try to create a calculated table with this:

Table = VAR _table1= TOPN( 10, data, [Total], ASC ) VAR _table2 = EXCEPT(data, _table1) VAR _table3 = ROW( "Category", "Others", "Total", SUMX(_table2, data[Total]) ) RETURN UNION(_table1, _table3)

it worked like this:

Re: Display first 10 values as is and remaining row values with specific name

DAXUser — Wed, 25 Jan 2023 09:07:20 GMT

TomasAndersson

This solved my problem. Thanks for the solution.