Pivot Tables Problem

Hello everyone,

I'm not a very active user (posting or replying), but I have been around reading and looking for new methods.

Currently I working on a dashboard and I'm "stuck" on a problem I think.

So I have a database that is broken in 4 milestones, for each milestone I have the following columns:
- Work complete (yes or no)
- Day (year, month, day), this records the day the above was changed from no to yes.
- Year (referent to where day falls)
- Week (referent to where day falls)
- user, that closed work in the system
- Company, where there user belongs

Like I said, each milestone has the same structure with same information (referent to that milestone).

Brief explanation how this works. I may have company A closing milestone 1, company B closing milestone 2..... So idea is to have 1 or more pivot tables per milestone to understand production.

My problem/s are:
- Problem 1: Can I have one single filter that filters the 4 milestones according with however closed them, having in mind that a column per milestone will have that data for each milestone.
- Problem 2: Same as above but for this it will be for the year. When I try to use a timeline it is only referent to one milestone, even if I connect it with the other pivot table it wont be accurate because only filter based on the milestone where the timeline was selected.

I have looked around on the forum, web in general but couldn't find anything. Either I'm not using the correct terms, or there is not info out there.

Any help would be very appreciated.

I'm trying to attach a sample file but doesn't open the Option, see below.

error.png

Hi,

Unfortunately pictures are of very little use in such cases. Consider uploading a workbook - it doesn't have to be the actual workbook in question; a small example using dummy data together with expected results will suffice.

Regards

Thanks, but I can see only one worksheet in there? No attempts at any Pivot Tables, nothing, so not sure what to go on in order to help.

Usually people have a go at something and then come to the forum when they've encountered an issue that they can't resolve.

Regards

Thanks. Can you give a practical example of what you are trying to achieve with reference to the file you posted? To me it's not clear what a "Milestone" is.

Regards

So in parts, milestones would be.

Milestone 1 - CABLE
Milestone 2 - SPLICE
Milestone 3 - TEST
Milestone 4 - RFS

Idea is to have a dashboard with the number for all 4 milestones and being able to filter by company. At this moment and as exemple I have only cable and splice and to filter the company I need to have one slicer or filter per milestone. Idea would be to have on comum that when filtering that filter would apply to all 4 milestones. Same idea for the year.

Hi,

Apologies for not replying but I find your current set-up a little too confusing to be able to help further. I would suggest that you create a much smaller workbook with a much simplified table containing dummy data.

Regards

As you are using the 2019 version of Excel perhaps this proposal that includes converting the original data into more of a proper data set using Get & Transform (Data tab) will help.
1. The table on the Sample sheet is converted into the table on the Table1 sheet using the following Power Query Advanced Editor code:

Please Login or Register  to view this content.

2. A pivot table is produced from the new table
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.