cancel
Showing results for 
Search instead for 
Did you mean: 
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 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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.