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
tbobolz
Resolver I
Resolver I

Challenging question....Help with Matrix displaying and counting row items in different columns

Hi all, i'm struggling to find a solution to the below data presentation. I have data that list patient records in each row as shown below. Each row display 5 physician types with doctors in each type, and they can be listed in multiple physician type on the same record. I want to pivot (Matrix) this data to display as I have shown below. Create a field called say "Physician" the list each physician, and display how many times that physician is list in each "physician type".

 

Additional, I want to have a total (or Physician Type as shown below in the example pivot) that list how many records the physician was listed on. Meaning, below the physician was listed in three record, even though the physician was listed a toal of 4 times throughout all fileds. I only wnant to count the number of time he/she was listed any given record. SO I would count Dr Patel as being listed in three records, even though on one record he was listed in two fileds

 

Finally, this data is connected to a sql cube that I can not change or add additional databases to. I can only create new measures.

 

Is this possible?

 

Thanks

Terry

 

HospitalPatient Account NumberAdmitting PhysicianAttending PhysicianReferring PhysicianPrimary Care Physician SurgeonCasesTotal ChargesExpected ReimbursementContribution Margin
Hospital North1345457842Dr PatelDr GoodDr SmithDr WrightDr Jones1500.65120.3260.54
Hospital North1345425687Dr PatelDr JonesDr SmithDr GoodDr Patel1601.25202.1285.64
Hospital North2346579654Dr GoodDr GoodDr RedDr WrightDr Jones1500.65120.3260.54
Hospital North2345896587Dr SkyDr JonesDr BlueDr GoodDr Patel1601.25202.1285.64
           
           
           
Pivot:          
           
Physician Physician TypeCasesTotal ChargesExpected ReimbursementContribution Margin     
Dr PatelAny position (Could be total line)31703.15524.56231.82  This line find the physician as exsisting in any given record (row) and returns totals
Dr PatelAdmitting Physician21101.9322.44146.18  This line returns each "physician type" they are listed in and returns totals
Dr PatelSurgeon21202.5404.24171.28  This line returns each "physician type" they are listed in and returns totals
           
 Then each Physician is repeated in the same manner.        
2 REPLIES 2
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @tbobolz,

 

Were you able to pivot table structure in Query Editor mode? If so, please refer to below steps.

 

1. Add index column

2. Pivot columns (select [Admitting Physician], [Attending Physician], [Referring Physician], [Primary Care Physician] and [Surgeon])

 

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    #"physician table_Sheet" = Source{[Item="physician table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"physician table_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hospital", type text}, {"Patient Account Number", Int64.Type}, {"Admitting Physician", type text}, {"Attending Physician", type text}, {"Referring Physician", type text}, {"Primary Care Physician", type text}, {"Surgeon", type text}, {"Cases", Int64.Type}, {"Total Charges", type number}, {"Expected Reimbursement", type number}, {"Contribution Margin", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Hospital", "Patient Account Number", "Cases", "Total Charges", "Expected Reimbursement", "Contribution Margin", "Index"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Physician Type"}, {"Value", "Physician"}})
in
    #"Renamed Columns"

1.PNG

 

Save above changes. Then, in report view mode, create measures like below:

Total charge per Physician =
CALCULATE (
    SUMX (
        DISTINCT ( 'physician table'[Index] ),
        FIRSTNONBLANK ( 'physician table'[Total Charges], 1 )
    )
)

Expected Reimbursement per Physician =
CALCULATE (
    SUMX (
        DISTINCT ( 'physician table'[Index] ),
        FIRSTNONBLANK ( 'physician table'[Expected Reimbursement], 1 )
    )
)


Contribution Margin per Physician =
CALCULATE (
    SUMX (
        DISTINCT ( 'physician table'[Index] ),
        FIRSTNONBLANK ( 'physician table'[Contribution Margin], 1 )
    )
)

 

Insert a Matrix visual to display data. Here, you need to select "Count(distinct)" aggregation for [Index].

2.PNG

 

However, if you were either not able to transform table in query editor, nor create calculated columns/calculated tables, it is not possible to achieve such a requirement with only measures.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yuliana,

 

Thank you very much for the time you pent helping me learn. It is very much apprecaited.

 

I do not have access to editing the tables. I can import the data as a csv and aviod the cube all together. I'm looking forward to trying your method, but I am a little confused as to the reference "source" = excel.workbook as I am not as experinced in PowerBI as I would like. Is this asking that I reference a master physician list?

 

Thanks again for your help and sorry for my delayed response, I was pulled away from the project for a while.

 

Terry

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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