CODEWITHSUNDEEP
powerbidax
Dan M
Dan M

Reputation: 820

Top N and Others in Power BI

I have a simple database with a doctors table and a detail table with their patients. How can I efficiently group doctors with less than 300 patients into an other doctors category to obtain a pie chart like the picture below?

enter image description here

What I end up doing is to create a DAX table

DoctorsWithPatientsCount = SUMMARIZE(Patient, Patient[Doctor.FullName], "PatientCount", COUNT(Patient[Id]))

with the following calculated column

TopDoctor = SWITCH(TRUE(), 'DoctorsWithPatientsCount '[Count] > 300, DoctorsWithPatientsCount[Doctor.FullName], "Other doctors combined")

and have that ploted.

As you can see it does work, however, is there another more direct and efficient way to achieve this? Thanks!

Upvotes: 2

Views: 1948

Answers (2)

davidebacci
davidebacci

Reputation: 30219

TopN and Others has been a long requested feature in PBI which you can vote on here: https://ideas.powerbi.com/ideas/idea/?ideaid=08369fa0-5152-4d55-b858-e36c06737d10

There are currently a few ways of achieving this.

  1. Your way which has the disadvantage of not being dynamic and responding to slicers because it is a calculated table. Usually, you would use a RANKX or TOPN rather than your DAX.

  2. If you want the TopN to be dynamic, you need a disconnected table. There are simple implementations described here: https://goodly.co.in/top-n-and-others-power-bi/ or more complicated but versatile approaches described here: https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

  3. You can use a custom visual like Deneb to perform a window transform that calculates the TopN for you and displays it automatically.

As an addendum, your pie chart should really only display 5 or 6 categories max to be useful to end users.

Upvotes: 1

Hengaini
Hengaini

Reputation: 44

In doctors table, you can define a new computed column "computed doctor"; then use this new computed column to make pie chart.

Upvotes: 0

Related Questions