[PQ] divide each row of a column by an item in the same column & add result to new column

[daliye]

Hi,
I am running a lot of analysis, using tables and a lot of formulas.
In the current example, I am running a vertical analysis, which is to divide all items of a column for a fiscal year by a single specific item.
So, in the example file, I want to add a new column for each year and divide each item by the total assets value of that year.
I hope anyone may have a clue about solving it.

[AliGW]

divide each item by the total assets value of that year.

What would this look like? Where have you mocked up expected results?

[daliye]

Thanks for the fast reply.
I've added a trial query inside the file but could not complete it correctly. I hope that it is clear, or do you need further information?

[AliGW]

How does a non-functioning query show me what you want???

Are these the type of extra columns you want?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G	H	I
31	2018 Balance	2018 %	2019 Balance	2019 %
32	28715836	1.85%	43612637	2.72%
33	36280	0.00%	36280	0.00%
34	3796	0.00%	18	0.00%
35	875855	0.06%	530279	0.03%
36	29631767	1.91%	44179214	2.75%
37	9915744	0.64%	9920274	0.62%
38	1811027	0.12%	374428	0.02%
39	21258377	1.37%	46673572	2.91%
40	59336104	3.83%	44733660	2.79%
41	107594745	6.94%	91265250	5.69%
42	199915997	12.89%	192967184	12.03%
43	229547764	14.80%	237146398	14.78%
44	40000000	2.58%	40000000	2.49%
45	2712830	0.17%	2771601	0.17%
46	13030865	0.84%	13279372	0.83%
47	55743695	3.59%	56050973	3.49%
48	0	0.00%	20000000	1.25%
49	17640000	1.14%	8108200	0.51%
50	15123480	0.98%	15123480	0.94%
51	32763480	2.11%	43231680	2.70%
52	44551339	2.87%	56545873	3.53%
53	26824819	1.73%	33846233	2.11%
54	32499112	2.10%	12750304	0.79%
55	390837	0.03%	669157	0.04%
56	36774482	2.37%	34052178	2.12%
57	141040589	9.09%	137863745	8.60%
58	173804069	11.21%	181095425	11.29%
59	229547764	14.80%	237146398	14.78%

Sheet: Sheet1

[daliye]

Yes, it is.
But a little thing is different, which is I want to divide each item in table b a specific item, which is "total assets", and not the total sum of the column.
In some other cases, the item will be something else and not the "total assets".
I thought about making a parameters list of items that will be used in calculations, then using it, but do not know how to fix it.
So, how to adjust it to be the total assets instead of the total of the column?

[AliGW]

Right - so please mock up a column to the RIGHT of your data table. Use the 2018 balance and give the value you expect to see in each row. To the right of that, explain the sum that you would have to do manually.

I am NOT going to waste time guessing. Your explanation is completely inadequate - sorry.

[daliye]

Thanks a lot, my friend.
I've reviewed your steps in detail and understood the idea, and then applied it.
File attached.
Thanks a lot

[AliGW]

And is that producing the results you expect?

I asked for a MANUALLY CALCULATED column (NOT created using PQ!) with your EXPECTED results.

[daliye]

Thanks for your valuable time helping me.
Yes, it does .
I am afraid that I do not understand what you mean by MANUALLY CALCULATED.
Do you mean that you are asking if I want to do it manually? or do you mean that I create a column manually with the expected results I want to be done using power query, so you can create it for me using power query?
Which one do you choose?
Or do you mean something else?

[AliGW]

Never mind - it's solved.

[daliye]

Yes, it is :D .

[daliye]

If I want to do some adjustments, shall I continue here or start a new thread?

[AliGW]

What type of adjustments? Tweaks here, additions in a new thread.

[daliye]

New addition.
New thread then.

[AliGW]

Yes, with appropriate title.

[daliye]

Lesson learned my friend.