Rollup Values for Child Records

[PhuongTN]

Hello, 

Below is a sample table that I have and I'm trying to find a way to calculate the values for the last 2 columns. Because Location Code 1 is a parent of Location Code 2, and Location Code 2 is a parent of both Location Codes 3 and 4, the rollup value is 215,000 (50,000+150,000+10,000+5,000), and so on for the other Location Codes. What is the formula to achieve that? Any kind of help will be appreciated.

Location Code	Parent Location Code	SumOfCostOf Replacement	Total Asset Count	Report CostOf Replacement	Report Total Asset Count
1	NULL	50,000	0	215,000	29
2	1	150,000	20	165,000	29
3	2	10,000	5	N/A	N/A
4	2	5,000	4	N/A	N/A

[v-juanli-msft · Accepted solution]

Hi @PhuongTN

Create calculated columns

parentmatch = CALCULATE(MAX([Parent Location Code]),FILTER(ALL(Sheet1),[Parent Location Code]=EARLIER(Sheet1[Location Code])))

allid = IF([parentmatch]=BLANK(),[Parent Location Code],[parentmatch])

parent = CALCULATE(MIN([parentmatch]),FILTER(ALL(Sheet1),[allid]=EARLIER(Sheet1[allid])-1))

category = IF([Parent Location Code]=BLANK(),[parentmatch],[parent])

Column 6 = IF([Parent Location Code]=BLANK(),CALCULATE(SUM(Sheet1[SumOfCostOf Replacement]),ALLEXCEPT(Sheet1,Sheet1[category])),IF([parentmatch]<>BLANK(),CALCULATE(SUM(Sheet1[SumOfCostOf Replacement]),ALLEXCEPT(Sheet1,Sheet1[allid]))))

Column 7 = IF([Parent Location Code]=BLANK(),CALCULATE(SUM(Sheet1[Total Asset Count]),ALLEXCEPT(Sheet1,Sheet1[category])),IF([parentmatch]<>BLANK(),CALCULATE(SUM(Sheet1[Total Asset Count]),ALLEXCEPT(Sheet1,Sheet1[allid]))))

Best Regards

Maggie

[PhuongTN]

That works like a charm!

Thanks Maggie.