Forum Discussion
Pivot with a mix of static and transactional data
Hi WillSmyth
yes, it's possible datamodel / Power Pivot.
You need to load the transaction table into the datamodel (menu "Power Pivot | Add to Data Model")
In the datamodel, you need to create a calendar table (menu "Design | Date Table | New)
Then create a relation between transaction table and calendar table based on the date field.
Create a pivot table from the datamodel (menu "Insert | PivotTable | From Data Model")
Add a timeline and use the date field from the calendar table (not from the transaction table!)
Open the PivotTable Option, Display tab and tick the checkboxes "Show items with no data on rows/columns"
I have done this already in the attached workbook.
Kind regards,
Martin
This works beautifully but I don't understand basing the relationship on the DATE field. Could you explain? (I will be marking as best response regardless!)
Hi WillSmyth
this is one of the advantages of calendar tables. They contain every single date and not only dates where any transaction has been registered in your table. Therefore the can show also those records with no transactions.
If you create the timeline with the date field from your transaction table, you will miss out account numbers that do not have any transactions at all (e.g. A1025, A1034, A1050...)
Kind regards,
Martin