Total Usage by Site from Same Columns But Different Rows

[eclipse75048]

I'm using PowerPivot with Excel 2010 to try to create a report on Video Conference Unit usage. I've only been using Pivot Tables for a couple of months and this is my first foray into the fabulous world of PowerPivot. I thought I had overcome all my newbie errors (date grouping, in particular) and then I realized that I wasn't getting a true site by site total. My universe of call total was correct, but not when broken down by each site.

I've pasted an extract of the source data below--in my source doc, there are a lot of other columns, but the five below are the ones I'm pulling in to the Pivot tables. Currently, I have about 9,000 rows of data, but that will increase by about 2,500 each month.

Can anyone recommend a way to total the usage for each site? Every method I've tried so far just gives me the total for either sOrigH323ID or the total for sDestH323ID. For example, I'm trying to make a pivot that shows the total usage for each site. Abilene had a total of 8 minutes of usage (7 inbound, 1 outbound.) I can't get my Pivot Table to show that--I would get either 7 minutes or 1 minute, depending on which Row Label is first.


[TABLE="width: 698"]
<tbody>[TR]
[TD]dtStartTime[/TD]
[TD]dtEndTime[/TD]
[TD]nConnectionTime[/TD]
[TD]sOrigH323ID[/TD]
[TD]sDestH323ID[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]FortMyers-FL-HR2-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]FortMyers-FL-HR2-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]Morgantown-WV-HR2-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]S9D[/TD]
[TD]LittleRck-AR-OI[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]56[/TD]
[TD]Houston-TX-HR1-[/TD]
[TD]Harlingen-TX-HR2-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]S9D[/TD]
[TD]LittleRck-AR-OI[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]S9D[/TD]
[TD]LittleRck-AR-OI[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]Syracuse-NY-HR5-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]Syracuse-NY-HR5-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]Birmingham-AL-TSC-B[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]Atlanta-GA-DTN-HR-A-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]LittleRck-AR-HR-C-[/TD]
[TD]PineBluff-AR-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]327[/TD]
[TD]Dallas-TX-N-HR2-[/TD]
[TD]Tyler-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]481[/TD]
[TD]LittleRck-AR-HR-C-[/TD]
[TD]PineBluff-AR-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]2[/TD]
[TD]Abilene-TX-HR1-[/TD]
[TD]Syracuse-NY-HR5-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]481[/TD]
[TD]Dallas-TX-DTN-HR1-[/TD]
[TD]Nacogdoches-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]153[/TD]
[TD]Midland-TX-HR2-[/TD]
[TD]FortWorth-TX-HR2-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD]Montgomery-AL-HR4-[/TD]
[TD]Abilene-TX-HR1-[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: center"]29[/TD]
[TD]LittleRck-AR-HR-B-[/TD]
[TD]Hot Springs-AR-[/TD]
[/TR]
</tbody>[/TABLE]

I'm sure there is a simple solution to this, but my brain is fried now! Any suggestion would be greatly appreciated!

Thanks!

Chris

 

[markmellow]

I'm not sure what method you used to get your totals which you found to be wrong and im in the process of mastering powerpivot myself but, have you tried these formulas =Sumif(),=subtotal(109,),=calculate()?

 

[miguel.escobar]

1. you need a new table with unique records for the sites
2. you need to make the appropriate relationships (1 will remain active and the other inactive but both of those relationships will be made against the new table)
3. make a simple formula using =SUM([sOrigH323ID]) + CALCULATE(SUM[the other column], USERELATIONSHIP(rel1,rel2) )

I hope this helps