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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
siddiq8686khi
Frequent Visitor

Help Required - TopN of Main and Subcategory

Hello Everyone,

 

I need help. I have a data of patients who visited hospital for their routine checkups. I have 4 data table for my analysis.

 

  • Patients visit and demographic table
  • Patient complaint table
  • Patient Diagnosis table
  • Patient medicine table

All the table have one main category column and one subcategory column. The sample data table of Complaint table is given below for your help:

Patient_ID

Date

Diagnosis_MainCategory

Diagnosis SubCategory

PAT-10019

24/05/2021

Anaemia

Type-1 Anaemia

PAT-10019

30/03/2022

Anaemia

Type-2 Anaemia

PAT-10019

25/05/2021

Anaemia

Type-2 Anaemia

PAT-10019

25/05/2022

Anaemia

Type-3 Anaemia

PAT-10019

28/03/2022

Anaemia

Type-3 Anaemia

PAT-10019

06/10/2021

Anaemia

Type-3 Anaemia

PAT-10019

09/11/2021

Anaemia

Type-3 Anaemia

PAT-10019

03/01/2022

Anaemia

Type-4 Anaemia

PAT-10019

21/01/2021

Anaemia

Type-4 Anaemia

PAT-10019

24/05/2021

Anaemia

Type-4 Anaemia

PAT-10019

16/02/2022

Anaemia

Type-4 Anaemia

PAT-10197

10/05/2022

Pneumonia

Type-1 Pneumonia

PAT-10197

29/01/2021

Pneumonia

Type-2 Pneumonia

PAT-10197

17/06/2021

Pneumonia

Type-2 Pneumonia

PAT-10197

18/03/2022

Pneumonia

Type-3 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-3 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-3 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

 

All these table connected with patient ID in data model. I need to calculate the below mentioned measure from the above 4 tables.

  • TOP 3 and Other for Main Category and Subcategory for all the tables
  • I want to show a chart of “OTHERS” in a tooltip. Here the OTHER is all those other than top 3.
  • I showed all the Complaints, diagnosis, and Medications in donut chart. I want that when I click any complaint the diagnosis chart cross filters and showed top 3 diagnosis and medication chart show top 3 medicine of the selected item in complaint chart.

I have created my TOPN, and others measure but it not working dynamically, and I am unable to open OTHERS category in a separate chart.

I will share my sample PBIX file if required.

@tamerj1 

 

Thanks,

Sid

 

 

4 REPLIES 4
siddiq8686khi
Frequent Visitor

Dear All,

 

 Below is the link of my Sample PBIX file for your help

 

https://www.dropbox.com/s/p6lw8yznb3hjwob/Sample_Data.pbix?dl=0

 

I have calculated top 3 + Others but i am unable to show the complaints which are falling in REMAINING CATEGORY if we use Top 3.

 

Thanks,

Sid

 

Looking for help

Hello All,

 

Any suggestions from you ??

amitchandak
Super User
Super User

@siddiq8686khi , Refer if these 2 can help

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=e0ce7d30-...

 

https://www.youtube.com/watch?v=JLK2e0k8OBE

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak Thanks for the reply. Actually i also need to calculate "Others ( All those who left after TopN will be shown as Others in Chatrts) means, if we will show a donut chart then there will be 6 slices - 5 slices for top5 and 1 for Others.

 

Thanks,

Sid

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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