Multi-retailer POS report

[ghardwick]

This is a total noob question I'm sure. I'm just getting started and this is my first time creating something in powerpivot. (I've used tools created by others in the past). I'm trying to build a dashboard with several charts and slicers.

I'm creating a POS report for a manufacturer taking several POS feeds from major retailers and linking the retailer's item numbers (this is how they report) to the manufacturer's number via a cross reference table. I'm able to get the sales to roll up to the manufacturer item number, but as three seperate measures (POS Qty), with a bar for each item. How do I get the POS qty's from each retailer to show as one number, then use the slicers to select individual retailers? Also, if I wanted to show a total topline trend, rolling all sales of all items into one monthly number how would I do that?

Thanks,

gh

 

[scottsen]

Really hard without seeing a pic of the model, but I get the feeling you basically have 1 lookup table (the manufactorer) and 3 fact tables (1 per POS system). If you don't want to combine the POS systems into 1 table (via power query or sql or appending csv or ... whatever) then you will need a measure something like:

total sales := sum(pos1[sales]) + sum(pos2[sales]) + sum(pos3[sales])

Your goal is that to basically only use filters/slicer and such from that manufactorer lookup.

 

[ghardwick]

I'm feeling like an idiot as I can't seem to create a calculated field. Do I need Excel 2013 for this? I have 2010.

So once the calculation is created I'd like for the chart to display topline sales for the retailers, selected via slicer, and also be able to drill down to brand or all the way to specific item and see sales at that level as well.

 

[scottsen]

You aren't an idiot -- microsoft is They changed the name -- in 2010 it's called a "Measure".