Show top n and "others" values

[DarioForm]

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.

[harshnathani · Accepted solution]

Hi @DarioForm ,

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)

[Anonymous · Accepted solution]

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

[DarioForm]

Thank you @Anonymous ,

this link is perfectly describing the solution!

regards,

Dario 

[stevedep]

@Anonymous , nice article indeed!

[DarioForm]

Hi everyone,

thanks to who already answered, specially @harshnathani 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

[harshnathani]

Hi @DarioForm ,

Please see 

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

Regards,

Harsh Nathani

[DarioForm]

Hi @harshnathani ,

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

[harshnathani]

@DarioForm ,

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

Regards,

Harsh Nathani

[DarioForm]

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].

[harshnathani]

@DarioForm ,

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,

HN

[DarioForm]

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.

[harshnathani · Accepted solution]

Hi @DarioForm ,

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)

[stevedep]

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. 

[DarioForm]

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

[stevedep]

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