topic Re: Show top n and "others" values in DAX Commands and Tips

Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 10:49:08 GMT

Hello everyone,

I want to create a measure that returns the top 5 values ranked by [measure] and the sum of the others in the same graphic.

My table columns are (granularity order ID):

- order ID

- sales

- quantity

- product name

and more metrics like order date, state, etc

The issue is that the [measure] I want to call to rank is switching metrics between sales and quantity, so it is hard to work on a calculated column;

moreover I would like the result to be dynamically filtered by other dim.

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 11:09:39 GMT

Hi Anonymous ,

Please see

https://community.powerbi.com/t5/Desktop/Rank-top-10-city-amp-rest-should-be-display-as-others/m-p/1143765#M519171

Regards,

Harsh Nathani

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 12:02:05 GMT

Hi Anonymous ,

thanks for the reply,

I've had already seen that solution but it cannot solve my issue because the calculated column is static and doesn't refer to the measure.

I also tried something like this https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

but it gives me an error

Re: Show top n and "others" values

stevedep — Fri, 05 Jun 2020 12:03:34 GMT

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 12:29:25 GMT

Anonymous ,

Can you pls share same data and formula of your measure .

Regards,

Harsh Nathani

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 13:34:28 GMT

My table is like:

Order ID	Product Name	Sales	Profit
1	Bike	120	10
2	Ball	40	2
3	Bike	200	25
4	Bike	250	20
5	Table	100	40

I have create this [measure] that, as the user select sales/profit, it returns total sales or total profit;
What I want to do is create a measure that can show the top 5 products and the others total value in the same graphic and can change the values related on what the user select in the first [measure].

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 13:48:57 GMT

Anonymous ,

are Sales and Profit measures or Column,

and if they are measure, how can a user Select Sales or Profit. Do you have a different table for selection?

Regards,

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 13:56:45 GMT

Yes well:

sales and profit are columns

for the selection I've create a disconnected table with only two data, "Total sales" (type: abc) and "Total profit" (type: abc), connected to the relative measure ( just a sum) so that, thanks to a switch, the user can select a word in the dashboard that changes the values due tu the context;

in this scenario I want to create the topx and others graphic.

Re: Show top n and "others" values

stevedep — Fri, 05 Jun 2020 17:53:58 GMT

Hi,

I got it working like described in the article you shared, which is very impressively done by that guy! Wow.

I used the table you provided and added a few more rows. Below you see it working for a top 2 scenario.

code:

topnmeasure = var __top = CALCULATETABLE(TOPN(2;VALUES('Table'[Product Name]);CALCULATE(SUM('Table'[Sales])))) var __other = ROW("Product Name";"Other") var __allTheRest = CALCULATE(SUM('Table'[Sales]);EXCEPT(VALUES('Table'[Product Name]);__top)) var __theUnion = UNION(__top;__other) var __selectedproduct = SELECTEDVALUE('Table (2)'[Product Name];"Multiple") return //CONCATENATEX(__top;[Product Name] & "m: " & [Measure]) //CONCATENATEX(__theUnion;[Product Name] & " m:" & [Measure]) //CONCATENATEX(INTERSECT('Table (2)';__theUnion);[Product Name] & " m:" & [Measure]) // it now has the top products and the other category SUMX(INTERSECT('Table (2)';__theUnion); IF('Table (2)'[Product Name]<>"Other"; CALCULATE(SUM('Table'[Sales]);'Table'[Product Name]=__selectedproduct);__allTheRest))

Link to the Power BI file here.

Hope this helps to move you forward, if so please mark as solution. Kudo's for the effort are appreciated.

Kind regards, Steve.

Re: Show top n and "others" values

Anonymous — Fri, 05 Jun 2020 18:32:00 GMT

Hi Anonymous ,

I have done this for Top2, you can change the value in TOPN function to 5.

If you want to show in matrix format.

Link to the file.

https://drive.google.com/file/d/1nMW9-djgSQlPPdUXvW2d0aFrqHrSXxU0/view?usp=sharing

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Show top n and "others" values

Anonymous — Sat, 06 Jun 2020 11:12:48 GMT

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

The link above has all you need to do this and even be able to drill down using the Other group.

Best
D

Re: Show top n and "others" values

Anonymous — Mon, 08 Jun 2020 07:31:43 GMT

Hi Steve,

I think that's the way of think to get the solution, but I still have problems with that 'table2'; I don't understand what it is.

Thanks for the answer,

regards Dario

Re: Show top n and "others" values

Anonymous — Mon, 08 Jun 2020 07:45:12 GMT

Thank you Anonymous ,

this link is perfectly describing the solution!

regards,

Dario

Re: Show top n and "others" values

stevedep — Mon, 08 Jun 2020 08:58:34 GMT

Anonymous , nice article indeed!

Re: Show top n and "others" values

Anonymous — Mon, 08 Jun 2020 14:18:37 GMT

Hi everyone,

thanks to who already answered, specially Anonymous and Anonymous for best solution for my situation.

There's something more I want to ask for, that's my result (top 5 and other):

Now if possible, I would like the graphic to show first the values of top 5 (in descending order), then at the end always the value of others, not depending on its size.

Regards,

Dario