Pivot Table showing a count AND and % of total

[mayday1]

I have 3 columns (A:C) - header names are PatientID, Reason, Paid

Reason is the reason for the visit, Paid shows if the visit has been paid for. So the data looks like this:

192837 Reason1 Y
459357 Reason2 Y
459379 Reason2 Y
192837 Reason1 Y
459357 Reason2 Y
342873 Reason3 N
345035 Reason2 N
459379 Reason2 Y

How can I do a pivot table that shows the results like this:
PatientID, Total number of visits by this patient, % of that patient's visits that have been paid for

Or is there a non-pivot table way to get this?

 

[sandy666]

with your example:
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PatientID[/td][td=bgcolor:#DDEBF7]Visits[/td][td=bgcolor:#DDEBF7]% of paid[/td][/tr]

[tr=bgcolor:#FFFFFF][td]192837[/td][td]

2​

[/td][td]

33.33%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]342873[/td][td]

1​

[/td][td]

0.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]345035[/td][td]

1​

[/td][td]

0.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]459357[/td][td]

2​

[/td][td]

33.33%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]459379[/td][td]

2​

[/td][td]

33.33%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

8​

[/td][td=bgcolor:#DDEBF7]

100.00%​

[/td][/tr]
[/table]

done with PowerQuery & PivotTable

or I missed something

 

[mayday1]

with your example:
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PatientID[/td][td=bgcolor:#DDEBF7]Visits[/td][td=bgcolor:#DDEBF7]% of paid[/td][/tr]

[tr=bgcolor:#FFFFFF][td]192837[/td][td]

2​

[/td][td]

33.33%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]342873[/td][td]

1​

[/td][td]

0.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]345035[/td][td]

1​

[/td][td]

0.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]459357[/td][td]

2​

[/td][td]

33.33%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]459379[/td][td]

2​

[/td][td]

33.33%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

8​

[/td][td=bgcolor:#DDEBF7]

100.00%​

[/td][/tr]
[/table]

done with PowerQuery & PivotTable

or I missed something

.....almost. Rather than a column showing a patients' percent of the total of paid visits of all patients, I'm looking for the percent of each individual patient's visits that are paid. So 192837 visited twice and paid both times, so for him it's 100%

 

[sandy666]

any example of expected result?

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PatientID[/td][td=bgcolor:#DDEBF7]Visits[/td][td=bgcolor:#DDEBF7]% of paid[/td][/tr]

[tr=bgcolor:#FFFFFF][td]192837[/td][td]

2​

[/td][td]

100.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]342873[/td][td]

1​

[/td][td]

#DIV/0!​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]345035[/td][td]

1​

[/td][td]

#DIV/0!​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]459357[/td][td]

2​

[/td][td]

100.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]459379[/td][td]

2​

[/td][td]

100.00%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

8​

[/td][td=bgcolor:#DDEBF7]

100.00%​

[/td][/tr]
[/table]

 

[mayday1]

That's exactly it. I can do the pivot table to get the PatientID and Visits columns - that's pivot table 101 easy. But how do I get that 3rd column to be the percent? Not sure on what to click on at all. Not that good with Excel

 

[sandy666]

load your source table to the PowerQuery editor then...

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PatientID", type text}, {"Reason", type text}, {"Paid", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Paid] = "Y" then "Y" else null)
in
    #"Added Conditional Column"[/SIZE]

after that you'll get fourth column

then insert PivotTable and use external source (QueryTable)
create three columns as you konw how, and use fourth column as count % of row Total
change the headers as you wish.

edit:
I forgot to say, fourth column of the result will contain error(s) #DIV/0!, so use Conditional Formatting :
format only cells that contain: Errors
and set font color : white

--
you can do that without PQ with formula : IF(Paid=Y,Y,"") and it will be your foruth column then use PivotTable as above with select table or range

 

[mayday1]

Been out a couple days.....

I'm lost already at "load your source table to the PowerQuery editor" I'm in Excel 2013. Where do I find that?

load your source table to the PowerQuery editor then...

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PatientID", type text}, {"Reason", type text}, {"Paid", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Paid] = "Y" then "Y" else null)
in
    #"Added Conditional Column"[/SIZE]

after that you'll get fourth column

then insert PivotTable and use external source (QueryTable)
create three columns as you konw how, and use fourth column as count % of row Total
change the headers as you wish.

edit:
I forgot to say, fourth column of the result will contain error(s) #DIV/0!, so use Conditional Formatting :
format only cells that contain: Errors
and set font color : white

--
you can do that without PQ with formula : IF(Paid=Y,Y,"") and it will be your foruth column then use PivotTable as above with select table or range

 

[sandy666]

If you don't have you can download and install PowerQuery add-in from here: Microsoft Power Query for Excel. Choose correct version. After install select your range or table and look for From Table. It will load your table to PQ editor.

here is help for PowerQuery: Microsoft Power Query for Excel Help

 

[sandy666]

you can do that without PQ with formula : IF(Paid=Y,Y,"") and it will be your fourth column then use PivotTable as above with select table or range

If you've trouble with PowerQuery there is an option without PowerQuery