Sum of rows for complex formula

[elaineng]

I need some help on the total on Column H, Row 3. The answer I want is 5 which is highlighted in Yellow.

A sample of the excel if uploaded here.

Dimension: =IF(ISNULL([Project Short Name]), ProjectName & ' - ' & PhaseName, [Project Short Name]) 

Column B / Column (1) = 

 sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=,
ConversionDate.autoCalendar.Date = {"$(='>=' & Date(YearStart(Today())) & '<=' & Date(QuarterEnd(Today())))"}>} TargetAmount)
/ 1000000 

Column C /Column (2) =

= Sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {">=$(=YearStart(Today()))<=$(=Today())"}>} NetGDV_Finance)
/ 1000000

Column D/ Column (3) =

= sum({$<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}, BookBreakdownStatus = {'SPA signed, pending capture'}>} NetGDV_Booked )
/ 1000000

Column E = (sum({$<ConversionDate=, Year = , Month = , [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}>} NetGDV_Booked )
)/ 1000000 

Column F = 

=((
((sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=,
ConversionDate.autoCalendar.Date = {"$(='>=' & Date(YearStart(Today())) & '<=' & Date(QuarterEnd(Today())))"}>} TargetAmount) // Current year Target Amount
)
-
(
Sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {">=$(=YearStart(Today()))<=$(=Today())"}>} NetGDV_Finance)
//YTD Sales
) // Booking Target to achieve
-
(
sum({$<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}, BookBreakdownStatus = {'SPA signed, pending capture'}>} NetGDV_Booked )

//Booked - Signed pending capture
))/0.5
)

-
( sum({$<ConversionDate=, Year = , Month = , [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}>} NetGDV_Booked )
) //Total Booked
) /1000000

Column G = [AVG Price]

=If(Dimensionality()<=1,
sum(Aggr(
Ceil(((
(
((sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=,
ConversionDate.autoCalendar.Date = {"$(='>=' & Date(YearStart(Today())) & '<=' & Date(QuarterEnd(Today())))"}>} TargetAmount) // Current year Target Amount
)
-
(
Sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {">=$(=YearStart(Today()))<=$(=Today())"}>} NetGDV_Finance)
//YTD Sales
) // Booking Target to achieve
-
(
sum({$<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}, BookBreakdownStatus = {'SPA signed, pending capture'}>} NetGDV_Booked )

//Booked - Signed pending capture
))/0.5
)

-
( sum({$<ConversionDate=, Year = , Month = , [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}>} NetGDV_Booked )
) //Total Booked
)/
(Week(Quarterend(today()),0)-week(today(),0))
)/
[Avg Price]), JV_Region)
)
,
Ceil(((
(
((sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=,
ConversionDate.autoCalendar.Date = {"$(='>=' & Date(YearStart(Today())) & '<=' & Date(QuarterEnd(Today())))"}>} TargetAmount) // Current year Target Amount
)
-
(
Sum({<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {">=$(=YearStart(Today()))<=$(=Today())"}>} NetGDV_Finance)
//YTD Sales
) // Booking Target to achieve
-
(
sum({$<ConversionDate=, Year = , Month =, [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}, BookBreakdownStatus = {'SPA signed, pending capture'}>} NetGDV_Booked )

//Booked - Signed pending capture
))/0.5
)

-
( sum({$<ConversionDate=, Year = , Month = , [ConversionDate.autoCalendar.Year]=, [ConversionDate.autoCalendar.Month]=, [ConversionDate.autoCalendar.Quarter]=, ConversionDate = {"<=$(=Max(ConversionDate))"}>} NetGDV_Booked )
) //Total Booked
)/
(Week(Quarterend(today()),0)-week(today(),0))
)/
[Avg Price])
)

[Kushal_Chawda · Accepted solution]

@elaineng  what is the expression you are using for column H? A quick try you can give as below

Wrap your expression into aggr

=  sum(aggr( Expression for  column H,  [Project Name],[Phase Name],[Project short Name]))

[elaineng]

It works. Thanks @Kushal_Chawda