Calculated Formula Pivot Table Values - DSO

[jmcconville18]

Hi,
I am trying to calculate a Weighted Days Sales Outstanding. I have invoice numbers, Dates Invoiced, Dates Cash Received, Company Name and Amount. The Rest of the information is calculated from those values.
Here's a sample from my spreadsheet.

Cash Received Date	Invoice #	Date Invoiced	Company Name	Credit Amnt	Days	$Days
1/6	33	1/1	Comp A	415.9	5	2079.5
2/28	32	1/10	Comp B	973.87	49	47719.63

I have calculated the difference between the two dates under the "Days" column and the product of (Credit Amnt * Days) under "$Days". I want my pivot table to Group information by the company. It will sum the Credit amount per company also the sum the number of my calculated column "$Days". From there I want to display a third column "Weighted DSO" that will divide the $Days by the Amount.

How can I get my pivot chart to show a bar graph with just the Weighted DSO as a value.


I have attached an excel file with an example of the data I am trying to gather.

Weighted DSO Example.xlsx

Any help is much appreciated

[judgeh59]

does this work for you?

I created a pivot table and then a calculated field. Basically it SUM(MyCalc)/SUM(Credit Amnt)....I changed the Column header in "G" because I was having other issues....HTH

[judgeh59]

I take it this helped....

Thanks for the rep points

[jmcconville18]

Yes it did. Thanks for the answer. I tried to put a comment when I added the rep points.

Have a great day

[judgeh59]

no problem at all...I didn't actually check the status before I shot my mouth off...thanks again and glad I could help....

You have a great day also