Log In
Register
Home
July 11, 2017
Offline
Hi
I am trying to create a report that only shows sites in a particular Group that have reduced GP% v previous week that are -10% or more.
In the table on the report tab I have gone as far as I can with formulas that show only these sites, but I do not know how to only pick up these sites and ignore all the other sites in the Group, e.g. currently there are 59 sites showing but I only want to pick up the 19 sites that are showing data, as I then want to create a chart from this table.
Any help greatly appreciated.
Wishing you all a Happy Holiday Season and a HEALTHY New Year.
Regards
Paul
July 11, 2017
Offline
Sorry, just realized first attachment was password protected.
July 11, 2017
Offline
forgot attachment, having a bad day
July 16, 2010
Offline
Hi Paul,
First, you need to put your data in a tabular layout. The data on the GP & Occupancy sheets is not in the correct format because it has multiple headings. Once they're in a tabular layout you can either use Power Query to bring the variance columns from the GP and Occupancy sheets into a single table. Or as shown in the file attached, you can use Power Pivot to create relationships between the two tables via the Sites & Groups fact table.
Then it's a simple job for a PivotTable to filter the data based on variances > -10%.
Mynda
1 Guest(s)
