Getting started with PowerPivot, need a nudge in the right direction

[CmdrKeene]

I'm pretty new to PowerPivot, and I've been pouring over sites like this, training at Lynda.com, official documentation at MS and MSDN, and purchased a Microsoft Press book about data modelling with PowerPivot.

And yet, I can't seem to build my equivalent of a "hello world" with it. I feel like I'm missing something pretty based.

I've been able to successfully create some great PivotTable reports by combing multiple tables in PowerPivot. Such as call records (I work at fulfillment center) related to other tables like call dispositions, employee information. This works when I have one "many" table (the call records) linked to several "one" tables: a list of all employees, a list of their locations, etc.

What I'm having a hard time doing is combing multiple "many" tables. For example, one of discreet call records, and another table of discreet orders placed (sales). This could be useful in calculating the sales rate per employee. I very much want to do this in PowerPivot rather than combining data with a billion VLOOKUP()'s.

So using PowerQuery, I've rolled up the call table and sales table by user and date. These tables show in my workbook and have been loaded into the Data Model for PowerPivot. So both show the user's total calls placed and total orders placed per day. How do I get these together? It almost seems like I need a table of discreet dates (literally a single-column table listing all 365 dates of the year)? Then link that to both of the other tables so I can get total calls and sales for each date?

I must be on the wrong track. Any tips? Is what I want even possible?

 

[78chris]

Hello

In most case you need a calendar table : create one in an Excel workbook (it's easy with a few formulas) and use it in all the data models needing a calendar.

Linking tables to this calendar helps to solve problems as yours and also allows comparing periods correctly. The calendar contains all days whereas our tables do not always, and this make a difference.

 

[zestyphresh]

With any Many<>Many relationship in Powerpivot you will need a table to 'Join' the fields you want to utilize in your report. In your specific example this may be by joining on a Date table (as recommended above) and a Salesperson table. That way when you add a Date or Salesperson to your pivot table rows/columns it will filter both tables when you add any values fields.

 

[CmdrKeene]

I will give it a shot. I knew I would have to get the salesperson in there to join the two tables (calls and sales), because the salesman's identity is different in each system. I figured I'd tackle date first.