Pivot table guidance required

[SarangManzoorMemon]

I have data set arranged as District n=3(G,S,K), Town n=18 (arranged as G=5,S=5,K=8), and Union Council n=176 (arranged as G=40,S=60,K=76) with values as Set Target (numbers), Coverage (number), % Coverage. remaining target to be covered after day 5.

I am asked to give daily 'point in time' comparison status separate day wise e.g. day 1 of Jan, Feb and March and day wise comparison separate (cumulative) e.g. day 1,2 Jan, Day 1,2 Feb, Day 1,2 Mar

Issues:

I need to calculate total for each day using pivot table when activity is completed for all 5 days and then remaining target to be achieved in coming days after 5 days, also i need to compare with previous months data. Everything goes fine till day 5 with way around by filtering. Now I am having issue compiling the remaining Target in one pivot. if i am asked to provide details of total achievement including day 5 total and day 6 separate having achievement against target. and there is no target for day 5 onwards, and this is needed on regular basis in a similar pattern. like: if today is 7, then i need to give target achieved up to day 5 and target achievement on day 6 and day 7 as well separately.

P.S. Sometimes, somethings are additionally included in target for the next month as well, only if found during day 6 and day 7.

[#2]

No replies. It means people are not reading or they may not have answers. Or they are too busy to even respond or they are ignoring this thread

[#3]

@SarangManzoorMemon 

Your question has had 93 views, so I suspect that those who read it simply do not understand it.

I for one certainly don't...

[#4]

@HansVogelaar if Target for 5 days is 5000 and achievement is 4500 in 5 days, 500 would remaining Target to achieve. This 500 would be Target for 6th day onwards.

I want pivot table to ignore target for day 6 which is 500, but consider only the achievement of day 5 + Day 6 and provides the result. For Example: if coverage for day 5 is 4500 and for day 6 is 200, so total achievement should be 4700/5000, not 4700/5500.

I hope you understand my question now. I do not want to use VBA as this would increase the file size and I do not have any expertise in PowerQuery or Powerpivot.

[#5]

@SarangManzoorMemon 

I hope that someone else can help you, I don't have the slightest idea. Sorry!

[#6]

No issues. You are the first one to respond that is commendable and I am highly thankful for your time to respond. Have a nice day. I wish you all the best.

[#7]

@SarangManzoorMemon 

You should provide the workbook and show the results you want.

[#8]

@Detlef_Lewin Below is a glimpse of small data which I need to compile. In the screenshot you can see that after day 5 Target changes, which i do not want pivot to consider while Totaling, Only the target of 5 days and adding up achievement of all days including day 5 and day 6.

I hope it is more clear now. Is there a workaround in pivot table to do this.

[#9]

@SarangManzoorMemon 

A screenshot is not a workbook.

[#10]

@Detlef_Lewin here is the attachment

[#11]

@SarangManzoorMemon 

I analysed you formula and rewrote them. Not really something for a pivot table.

But I think that is not what you want.

[#12]

Thanks for your efforts.

I want the summary in pivot table form. Target should be up to Day 5.

DownTown/Cantt Target for all five days = 8000, achievement is for five days is 6250, so the remaining Target would be 8000 - 6250, which would be Target for day 6 and I want results as
Target = 8000 which is target of 5 days, here target of day 6 should not be considered in the pivot table.

Now if i want achievement of 6 days, where day 6 target is not required, only achievement is required. How can I do it.

[#13]

@SarangManzoorMemon 

You still have not shown what the pivot table should look like.

But I guess the only way to solve it would be DAX measures. And that is not my expertise - yet.

[#14]

Yes, indeed. I also got the similar idea from other colleagues, also they suggested to use VBA, in both I am 1/10, so not going to have this ease to i will go manual mode. Or try to explain my supervisor that it is not possible simply. By the Thanks for being with me and replying to my every query and being so much patient quite appreciated.
Bundle of Thanks Dear.

[#15]

Note: VBA is not DAX and DAX is not VBA.

[#16]

Yes, indeed. VBA is coding and DEX is used Power-Query and Power-Pivot.