# Calc and Sum for multiple tables

#### one3nine0

##### Board Regular
Sorry, this has probably been hashed out a million times, but I just can't get it to work.

I have two tables Raw Data and Zip Data. Relevant columns are
Raw Data: Location ID, Date, Qualified Calls
Zip Data: Location ID

I want to create a new column in powerpivot to calculate the number of calls per Location ID for between Jan 1, 2014 and Jan 31, 2014 in the Zip data table

This is the formula I came u with
=CALCULATE(SUM(RawData[Qualified Calls]), DATESBETWEEN(RawData[Date], date(2015,01,01),date(2015,01,31)), filter(RawData,RawData[Location ID]=ZipData[Location ID]))

But this returns the sum of all the locations for every single row, instead of by location ID in the ZipData table

What Can I do so that calculate sum, or any calculate formula will filter by location ID???

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
First off, if you want to use DATESBETWEEN, you will need a "real" calendar table. See Power Pivot Date Table | Power Pivot | Tiny Lizard

2nd, it is not clear to me if there is a relationship between your 2 tables. Is Zip Data a "lookup table" with just unique values?

Correct, ZipData is just a table with unique values placed in it. I want to add another column with total calls for each location based on the qualified calls column in RawData table.

I know i can do this through sumif in excel, but I want to try to learn to do this through power pivot.

I linked the tables through location ID on both tables through powerpivot.

And if I cannot use the datesbetween function, how do i make sure that the calculate function is only adding from dates in January?

Let's see how close we can get with... not much effort

Create a calculated field (aka "Measure" in 2010)

Total Calls := SUM(RawData[Qualfied Calls])

Create a pivot table with ZipData[Location] on rows, and drop Total Calls in values.

Now you can just add RawData[Date] as a report filter or Slicer and... select the dates you care about.

If really want that in a measure as well... something like:
=CALCULATE([Total Calls], FILTER(RawData, RawData[Date] >= DATEVALUE("1/1/2015") && RawData[Date] <= DATEVALUE("1/31/2015")))

Though... you would be much better off with a calendar table, linked to RawData[Date]... so you can just use your [Total Calls] against Jan 2014, Jan 2015, do YoY% Change, All of 2014, Q1 of 2015... you get the idea

That worked well, Thanks!

I guess I never got used to using "Measures/Calculated Fields" so approaching it that way is something new.

Thanks again

Replies
2
Views
115
Replies
3
Views
154
Replies
0
Views
350
Replies
0
Views
744
Replies
3
Views
444

1,203,182
Messages
6,053,972
Members
444,695
Latest member
asiaciara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back