Summarizing values in multiple tables

[tourgis2000]

Hi all,

I'm trying to find a way of creating a simple pivot that looks like this:

Fruit	Sales
Apples	3
Pears	5
Oranges	2
Total	10

<tbody>
</tbody>

The data for each type of fruit is stored in a different table because each type of fruit has a lot of unique properties I need to work with later. This is probably a stupid question but how do I create a relationship/measure that will enable me to produce this result?

Many thanks,

Martin

 

[MD610]

Are any fields common to all 3 tables? You need some common thread with a transaction ID or a date or a customer ID to do any kind of relationships in the first place.

The ideal situation would be to merge all three tables into one before loading to PowerPivot. Eventhough their properties are unique, do that have similar fields that you could line up? Otherwise can you create one giant table with all unique fields from each of the 3 tables and then copy the data into the larger table. Just leave the fields null that don't apply to a given fruit type.

Without any relationships, you could create a sales measure for each table and then a 4th measure that totals the 3 sales measures.

[Apples]:=COUNTROWS('Apple Table')

[Pears]:=COUNTROWS('Pears Table')

[Oranges]:=COUNTROWS('Oranges Table')

[Total]: = [Apples]+[Pears]+[Oranges]

Once you have all 4 measures, create a pivot by dragging the measures into the Values area. Once all four measures are in the values area, by default you should see a Values heading in your Column Labels. Drag that to Row Labels instead and you will end up with a pivot that resembles the example you provided above.

 

[MD610]

Actually after thinking about this, there is an additional step that will improve this and make it a true pivot table.

What I did above mimics the layout you wanted but doesn't really function like an actual pivot.

However if each table has a type field that lists whether it is apple, pear, or orange (or an ID for that type of fruit) you could create a 4th table with unique values for that fields and then use that to relate all 3 tables.

So your 4th table would either be one or two columns like this:

Type
Apples
Pears
Oranges

Assuming your fruit tables each have a column that lists the type for every record (if it doesn't you should be able to easily add one), just set up a relationship with all 3 fruit tables to the new Type table.

In your pivot, drag the Type field from the new Type table into your Row labels. Drag the [Total] measure that I outlined in the previous post to Values. You should now have a true pivot showing the summary info of all 3 tables.

 

[tourgis2000]

Thanks MD610,

In fact I already implemented a 4th table as you suggested though my method of calculating the results was to put my 'Fruit Type' into Row and this measure into Value:

=SUM(Fruit_Apples[Sales])+SUM(Fruit_Oranges[Sales])+SUM(Fruit_Pears[Sales])

It worked anyway. But now I'm stuck on how to make a pivot for year on year, quarter on quarter data. I've got all the date information in each 'Fruit_X' table and a separate date table for Quarter, but I'm very confused by how to implement this. Any help greatly appreciated.

Thanks again,

Martin

 

[MD610]

For date tables you need a table that lists every single date from the first date in your data to the last date in your data and everything in between regardless of whether or not the "in-between" dates actually appear in your data. Date tables can have no gaps in the dates. It should have at least these 4 columns:

1. FullDate - This is basically the unique ID of your date table and what you will use to relate it to the Fruit tables. As such it should be formatted the same as the dates in your fruit tables.
2. Year - Just the year of the FullDate
3. Month - Month of the FullDate
4. Qtr - Quarter of the FullDate

Once created and loaded to PowerPivot, create relationships from all 3 Fruit Tables, using their date field, to the FullDate field of the date table. You should end up with the 3 fruit tables all related to the 4th Type table that you already created and now all 3 Fruit tables should also be related to your date table too.

If done properly, you should be able to use any of the date elements in rows, columns, filters, or slicers to view your sales sum measure.

 

[tourgis2000]

Thanks again MD610,

I managed to fudge my way through this. Now I'm sitting down with a book to learn it properly!

Martin