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

[tbobolz]

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

Hospital	Patient Account Number	Admitting Physician	Attending Physician	Referring Physician	Primary Care Physician	Surgeon	Cases	Total Charges	Expected Reimbursement	Contribution Margin
Hospital North	1345457842	Dr Patel	Dr Good	Dr Smith	Dr Wright	Dr Jones	1	500.65	120.32	60.54
Hospital North	1345425687	Dr Patel	Dr Jones	Dr Smith	Dr Good	Dr Patel	1	601.25	202.12	85.64
Hospital North	2346579654	Dr Good	Dr Good	Dr Red	Dr Wright	Dr Jones	1	500.65	120.32	60.54
Hospital North	2345896587	Dr Sky	Dr Jones	Dr Blue	Dr Good	Dr Patel	1	601.25	202.12	85.64
Pivot:
Physician	Physician Type	Cases	Total Charges	Expected Reimbursement	Contribution Margin
Dr Patel	Any position (Could be total line)	3	1703.15	524.56	231.82			This line find the physician as exsisting in any given record (row) and returns totals
Dr Patel	Admitting Physician	2	1101.9	322.44	146.18			This line returns each "physician type" they are listed in and returns totals
Dr Patel	Surgeon	2	1202.5	404.24	171.28			This line returns each "physician type" they are listed in and returns totals
	Then each Physician is repeated in the same manner.

[v-yulgu-msft]

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"

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].

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

[tbobolz]

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