Hi,
I made a closing control sheet for which I would like to make a pivot table with following sorting view
First Section (from financial statement), then Frequency (of execution) and then Business day (of execution)
I learned a nice trick in a webinar of Mynda (Interactive Excel Dashboards with Power Query and Power Pivot), so I executed following steps
- Add a sort column in those 3 "Characteristic" tables, move them to the data model & create link with the data table
- Hide those Characteristics in data table
- Create Pivot with fields from Characteristic tables
Issue : a line which is shown only once in the data table is repeated where it shouldn't
Is there a way to avoid this?
See files attached
Kr Claudine
Hi Claudine,
Your model is missing relationships between the Frequency and Business Day tables and your Closing Control Sheet. You have fields from your Frequency and Business Day tables in your PivotTable, but Excel doesn't know how they relate to the Values field 'Activity'. This is why there is a yellow warning box above the field list stating 'Relationships between tables may be needed'.
If you add the relationships the PivotTable only contains one row.
I hope that points you in the right direction.
Mynda
Hi Mynda,
all looks fine now! thank you for solving my query
Kind regards,
Claudine
