cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors