TOTAL Column

[#1]

Hi

Attached herewith is my sample QV Buying Model. I have attached my data source which is in excel.

In my chart table, i have a stock required column. I want to create a TOTAL STOCK REQUIRED column per item ( ie i want to create the column highlighted in yellow).

How do I do this?

kind regards

Nayan

Please note in the table below, I have hidden other columns , so that the table looks legible.

Item Code	Branch	Stock Required	TOTAL Stock Required
ABC	Bloemfontein	2 330	16 848
ABC	Botswana	403	16 848
ABC	Cape Town	2 821	16 848
ABC	Durban	0	16 848
ABC	East London	101	16 848
ABC	Johannesburg	9 440	16 848
ABC	Namibia	185	16 848
ABC	Nelspruit	21	16 848
ABC	Polokwane	1 536	16 848
ABC	Port Elizabeth	0	16 848
ABC	Swaziland	13	16 848
ABC	Upington	0	16 848
ABC	Total	16 848	16 848
DEF	Bloemfontein	0	913
DEF	Cape Town	210	913
DEF	Durban	0	913
DEF	East London	0	913
DEF	Johannesburg	422	913
DEF	Namibia	107	913
DEF	Nelspruit	0	913
DEF	Polokwane	0	913
DEF	Port Elizabeth	175	913
DEF	Swaziland	0	913
DEF	Upington	0	913
DEF	Total	913	913
GHI	Bloemfontein	0	8 315
GHI	Botswana	1 612	8 315
GHI	Cape Town	1 564	8 315
GHI	Durban	3 447	8 315
GHI	East London	0	8 315
GHI	Johannesburg	0	8 315
GHI	Namibia	315	8 315
GHI	Nelspruit	0	8 315
GHI	Polokwane	0	8 315
GHI	Port Elizabeth	1 106	8 315
GHI	Swaziland	0	8 315
GHI	Upington	272	8 315
GHI	Total	8 315	8 315
Total		26 075	26 075

[PrashantSangle · Accepted solution]

Hi,

Use below expression to get Total Stock Required

sum(TOTAL<[Item Code]> aggr(rangemax(0,$(vAvgLast6Months)*$(No of Months)-sum([Quantity on Hand])),Branch,[Item Code]))

Regards

[MK_QSL]

You need to use Total Function with grouping of Item Code some thing like below

NUM(sum(TOTAL <[Item Code]> aggr(rangemax(0,$(vAvgLast6Months)*$(No of Months)-sum([Quantity on Hand])),Branch,[Item Code])),'##0')

[#4]

Than you Max and Manish.  Both your formulae worked.

kind regards

Nayan