Pivot Table - Column is repeating itself with each month - Trend Analysis

[hananak]

Dear All,

I am trying to do Trend analysis on monthly basis for Revenue/Direct cost etc and at the end I put Annual budget. I use Pivot table to do trend analysis. Currently the problem is when I drag Annual budget into value field, the column repeats it self for each month despite being having the same value, as the Annual budget will remain same the whole year.

Is there a way to have the Annual Budget appear only once at the end and not to repeat with each month?

I have the option to either use regular Pivot table or Power Pivot and Power Query or any combination of these, so solution related to any of these will work for me.

Please see the attached picture to understand the source data structure and the output when I create a pivot table - as you can see the Annual Budget is repeating with each month.

Sorry, on the picture there is a typo - I wanted to write Bottom and instead wrote Button.

Your expert advice would be really appreciated.

 

[RoryA]

If you use Power Pivot you can create a column set to only show the budget at the grand total level. You could also use Power Query to create a pivot of the values and then append a new column of the budget totals calculated in another query, or a direct calculation.

 

[hananak]

If you use Power Pivot you can create a column set to only show the budget at the grand total level. You could also use Power Query to create a pivot of the values and then append a new column of the budget totals calculated in another query, or a direct calculation.

Thanks for your reply. Would you be able to provide a sample working solution based on your suggestions?

I am not really good at Power Pivot or Power query. So really appreciate if you could help?

 

[RoryA]

If you provide a sample file, I probably can.

 

[hananak]

If you provide a sample file, I probably can.

Please see the mini sheet for my source data, you will have to create a pivot table to see that Annual budget is appearing again and again with each month. Your help will be really appreciated. Thanks.

Trend Analysis V3.xlsx
	A	B	C	D
1	Months	Account Code	Annual Budget	In Month Actuals
2	202301	Revenue	(1,766,244.00)	(144,687.00)
3	202301	Direct Costs	(2,262,852.00)	(191,370.75)
4	202301	Direct Costs	(2,262,852.00)	(46,666.67)
5	202301	Indirect Costs	(1,171,968.00)	(46,666.67)
6	202302	Revenue	(1,766,244.00)	(16,894,117.58)
7	202302	Direct Costs	(2,262,852.00)	(191,370.75)
8	202302	Direct Costs	(2,262,852.00)	266,893.16
9	202302	Indirect Costs	(1,171,968.00)	(16,666.67)
10	202303	Revenue	(1,766,244.00)	(7,356.00)
11	202303	Direct Costs	(2,262,852.00)	(16,666.67)
12	202303	Direct Costs	(2,262,852.00)	(11,805.07)
13	202303	Indirect Costs	(1,171,968.00)	(7,356.00)
14	202304	Revenue	(1,766,244.00)	(19,121.42)
15	202304	Direct Costs	(2,262,852.00)	(2,399.23)
16	202304	Direct Costs	(2,262,852.00)	(11,805.07)
17	202304	Indirect Costs	(1,171,968.00)	(15,208.00)
18	202305	Revenue	(1,766,244.00)	(2,172.70)
19	202305	Direct Costs	(2,262,852.00)	(2,399.23)
20	202305	Direct Costs	(2,262,852.00)	(19,121.42)
21	202305	Indirect Costs	(1,171,968.00)	(483,240.81)
22	202306	Revenue	(1,766,244.00)	(29,503.23)
23	202306	Direct Costs	(2,262,852.00)	(34,327.91)
24	202306	Direct Costs	(2,262,852.00)	(483,240.81)
25	202306	Indirect Costs	(1,171,968.00)	(116,519.10)
26	202307	Revenue	(1,766,244.00)	(34,327.91)
27	202307	Direct Costs	(2,262,852.00)	(116,519.10)
28	202307	Direct Costs	(2,262,852.00)	(29,503.23)
29	202307	Indirect Costs	(1,171,968.00)	(191,921.57)
30	202308	Revenue	(1,766,244.00)	(34,327.91)
31	202308	Direct Costs	(2,262,852.00)	(8,277.37)
32	202308	Direct Costs	(2,262,852.00)	(13,333.34)
33	202308	Indirect Costs	(1,171,968.00)	(8,226.67)
34	202309	Revenue	(1,766,244.00)	(13,333.34)
35	202309	Direct Costs	(2,262,852.00)	(8,226.67)
36	202309	Direct Costs	(2,262,852.00)	(12,027.59)
37	202309	Indirect Costs	(1,171,968.00)	(18,955.88)
38	202310	Revenue	(1,766,244.00)	(18,955.88)
39	202310	Direct Costs	(2,262,852.00)	(12,027.59)
40	202310	Direct Costs	(2,262,852.00)	(73,471.35)
41	202310	Indirect Costs	(1,171,968.00)	(15,253.00)
42	202311	Revenue	(1,766,244.00)	(12,575,933.00)
43	202311	Direct Costs	(2,262,852.00)	(15,253.00)
44	202311	Direct Costs	(2,262,852.00)	(15,253.00)
45	202311	Indirect Costs	(1,171,968.00)	1,205.04
46	202312	Revenue	(1,766,244.00)	(8,400.00)
47	202312	Direct Costs	(2,262,852.00)	(194,752.42)
48	202312	Direct Costs	(2,262,852.00)	(92,112.58)
49	202312	Indirect Costs	(1,171,968.00)	(194,752.42)
Data

 

[RoryA]

Make that data into a Table.
Use the Data - From Table/Range option.
In the PQ editor, select the Months column and on the Transform tab click Pivot Column:

Choose the In Month Actuals column for the values and press OK. You should now have the output table you want that you can then load to a worksheet.

 

[hananak]

Make that data into a Table.
Use the Data - From Table/Range option.
In the PQ editor, select the Months column and on the Transform tab click Pivot Column:
View attachment 122215

Choose the In Month Actuals column for the values and press OK. You should now have the output table you want that you can then load to a worksheet.

Please see the attached Picture. The problem is PQ create 12 columns for each month and based on this new data structure, I'll have to bring every time when new month's data is available to analyse the trend. Is there any possibility to keep the current structure and when creating Power Pivot not to repeat Annual budget column with each month?

I believe, you suggest something in Power Pivot.

Please see the attached picture.

 

[RoryA]

I have no idea what you mean. What I suggested results in this:

and new months will automatically be added to the end.

 

[hananak]

I have no idea what you mean. What I suggested results in this:

View attachment 122217
and new months will automatically be added to the end.

The issue is I do not want to create 12 columns using PQ. I want to keep only 1 column called In Month Actuals in the power pivot and in the filter i can choose for how many months I would like to see the trend and only one column at the end for Annual budget, exactly the way you are showing but in power pivot. I use power pivot for analysis as easy to manipluate data.

Currently in power pivot when I drag the In Month Actuals and Annual Budget in the value field and Months into Column field, the Annual budget columms repeats itself. If you kindly look at the another picture and 2nd pivot table,I uploaded. Hopefully that will clarify the confusion.

 

[RoryA]

I have the option to either use regular Pivot table or Power Pivot and Power Query or any combination of these, so solution related to any of these will work for me

Would have been good if you had specified that the output had to be a pivot table rather than suggesting it could be anything.

You will need to create a column set for your pivot and remove any columns that show the budget where you don't want them.