Hello John and Mynda - I'm a newbie to your wonderful Xtreme Pivot Table training and hope there is a good solution to my challenge.
I've been working to build an excel product that attempts to show my Boss the company's "active" projects across the coming months of 2022. Attached is a screenshot of my challenge.
Each row is a set of data for a given effort that is very suitable for Pivot Table analysis (e.g. value, owner, project title, etc.). Starting in column N, I've used IFS formulas to populate a "tag" in the respective month cell as shown depending upon the dates in column J, K, and L relative to the monthly dates starting in column N. In a given month - a project can be "blank", "DRFP", "Exp Final", or "Live" depending upon the data (as shown in the attachment). Up in Rows 2 through 5 are basic summary counts of the number of project states in the given month. That seems to be working fine to create this visual "waterfall" of project activity across the respective months for a visual of our company workload to our Leadership as well as a tally of the projects in their states for that given month. A challenge is when someone filters on a column of data to see just a given Owner's projects, or only projects above a selected value - the tallies in row 2 through 5 don't change (obviously they count ALL projects in the month) causing users to have to manually count the filtered states.
There may well be a better way to solve this analysis problem - but the Boss wanted a sort of waterfall visual for his Bosses to understand and this is what I came up with.
As I take the Xtreme Pivot Table course (and I haven't yet gotten to the Pivot Chart section yet - maybe that can more easily provide the desired waterfall visual - I'll get to it soon!) I wonder if there is a way to somehow create a PT to be able to analyze the Project data AND the state in a given month so that slicers for say "February" can be selected and the numbers of projects in a given state for Feb are provided - along with the usual PT statistics (e.g. sum of the value of February projects, etc.). Unfortunately - I haven't figured out a way to lay out this waterfall data in a manner that a Pivot Table could use. I'm coming to the conclusion that I need to somehow (Power Query?) list all 57 projects for every month of 2022 with their respective monthly state listed in the table data set - which would seem to be an option - just not sure how to make that transition from this visual waterfall to a new sheet with the data that captures the states across the months and it NOT be a major manual burden for all months and any updates. (We pull a corporate report frequently on our projected projects, place it into a data sheet, and formulates update the data and waterfall presentation adjusts as needed automatically - happy with that as a first effort).
I would really appreciate any ideas on how to solve this problem.
Thank you!
Max
Hi Max,
I think your table is fine and you can enhance the filtering functionality with Slicers for Excel Tables. To fix the count information, you can use the AGGREGATE function to ignore filtered rows.
Hope that points you in the right direction.
Mynda
Mynda - THANK YOU! I was not aware of the AGGREGATE function in my work to date (don't know what I don't know) and that looks like exactly what I need.
Further - I jumped into slicers for Pivot Tables with both feet to great effect but had no idea that slices would work with tables too!
I'm excited to add both to my Excel toolkit!
Additional Question: As mentioned in the original post - I'm creating the color-coded entries in a given week using a lengthy IFS formula with conditional formatting for the color to account for all the different cases. I've about finished John's Pivot Chart material in the Xtreme Pivot Chart course (but I'll confess I haven't yet reviewed the Charting piece in your Excel course that I'm taking concurrently) - is there any Excel charting option to automatically create such a Waterfall presentation as a "chart"? I'm thinking not or you would have provided that hint in your above answer - but just hoping... 🙂
My goal for the next version of this forecasting tool is to automate it with supporting macro "buttons" to populate once - use the data many across the multiple tabs of this product.
THANK YOU for your prompt reply and guidance!
Best regards,
Max
Hi Mynda -
So the AGGREGATE function is pretty cool - but unless I'm missing something - it doesn't solve my problem. I'm using COUNTIF to sum the number of instances of the four options in a given week in that grouping at the top of the screenshot - and everything I'm reading is that AGGREGATE doesn't do that, for example:
=AGGREGATE(2,3,tblMonthly[1/1/2022]) counts ALL the entries in the 1/1/2022 week column and works with filters, but
=AGGREGATE(2,3,tblMonthly[1/1/2022]="DRFP") to count only the DRFP entries in the week results in a #VALUE error.
I will think about a Table slicer option - assuming the slicer can be linked to all 12 columns for the months of 2022 like they can in a Pivot Table - I haven't tried that yet.
Unless there is something within AGGREGATE to permit a COUNTIF type feature - I'm back to looking at other options, like building a VBA function to count the different instances, ignoring hidden rows due to any filtering.
Any thoughts?
Thank you,
Max
Hi Max,
My bad, I misread your request. For the summary table you will have to use a formula like the one below:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N100,ROW(N8:N100)-ROW(N8),0,1)),--(N8:N100="Live"))
You can't use a single Slicer to filter every date column in a table. Slicers are one per column, therefore you could add separate Slicers for the RFP Start or Due Date columns, but not a single Slicer for the columns with dates in the column labels.
You also can't use this data in a PivotTable because the dates are spread across multiple columns, in other words, your data is not in a tabular layout. You would need to unpivot the data with Power Query before you can use it in a PivotTable.
Mynda
Mynda - Thank you for your kind reply - I will try the formula you suggest for the conditional tallies and add Power Query to my learning "to do" list.
Thanks again,
Max
Mynda - your above-suggested SUMPRODUCT(xxxx) formula works PERFECTLY! And even better - I learned so much utility by breaking down the formula into its component parts and understanding the logic (vice just pasting the magic formula) to achieve the desired result. My non-accounting work hasn't had much use for SUBTOTALS or OFFSETS and I had never seen the use of a double negative ('--') in a formula before - but figured it out.
I'm definitely adding this knowledge to my excel toolkit for the next time it is needed.
My Boss will be SO happy to have an updated report with this enhanced functionality.
Thank you!
Max
