Help Required - TopN of Main and Subcategory

[siddiq8686khi]

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

[siddiq8686khi]

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

[siddiq8686khi]

Hello All,

Any suggestions from you ??

[amitchandak]

@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

[siddiq8686khi]

@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