Calculation by Category

[Anonymous]

Hello All, 

I have a table 

need to add a calculated column the calculation is 

Paid Cost = sum of Planned cost + Sum of Substation/count of Circuit 

Substation	Circuit Name	Planned Cost
Ardmore	Ardmore 24	$14,793
Ardmore	Ardmore 24	$32,603
Ardmore	Ardmore 24	$40,000
Ardmore	Ardmore 24	$118,000
Ardmore	Ardmore 24	$383,180
Ardmore	Ardmore 24	$1,064,590
Bellcow	Bellcow 21	$19,279
Bellcow	Bellcow 21	$26,091
Bellcow	Bellcow 21	$35,000
Bellcow	Bellcow 21	$190,000
Bellcow	Bellcow 21	$463,180
Bellcow	Bellcow 21	$848,958
Bellcow	Bellcow 50	$12,476
Bellcow	Bellcow 50	$25,000
Bellcow	Bellcow 50	$27,383
Bellcow	Bellcow 50	$124,500
Bellcow	Bellcow 50	$298,180
Bellcow	Bellcow 50	$902,740
Bellcow	Substation	$1,919
Bellcow	Substation	$8,392
Bellcow	Substation	$15,000
Bellcow	Substation	$50,000
Bellcow	Substation	$84,330
Bellcow	Substation	$200,000

for Ardmore there is no Substation so the Paid Cost = sum of Planned cost which is $1,653,166

Substation	Circuit Name	Planned Cost	Paid Cost
Ardmore	Ardmore 24	$14,793	$1,653,166
Ardmore	Ardmore 24	$32,603	$1,653,166
Ardmore	Ardmore 24	$40,000	$1,653,166
Ardmore	Ardmore 24	$118,000	$1,653,166
Ardmore	Ardmore 24	$383,180	$1,653,166
Ardmore	Ardmore 24	$1,064,590	$1,653,166

but for Bellcow we have 2 Circuit and substation Note the 2 is the count of Circuit for Bellcow it will be different for each Circuit 

sum of Bellcow 50=  $1,390,279 (124,500+902,740+12,476+27,383+298,180 +25,000)

sum of Bellcow 21 = $1,582,508(190,000+848,958+19,279+26,091+463,180+35,000)

sum of Substation = $359,641(84,330+15,000+50,000+200,000+1,919+8,392

now the Paid Calculation =  sum of Bellcow 50+(sum of Substation/2)

                                              sum of Bellcow 21+(sum of Substation/2)

Substation

Circuit Name

Circuit Number

Planned Cost

Paid Cost

Bellcow	Bellcow 50	710450	$        124,500	$1,570,099.41
Bellcow	Bellcow 50	710450	$        902,740	$1,570,099.41
Bellcow	Bellcow 50	710450	$          12,476	$1,570,099.41
Bellcow	Bellcow 50	710450	$          27,383	$1,570,099.41
Bellcow	Bellcow 50	710450	$        298,180	$1,570,099.41
Bellcow	Bellcow 50	710450	$          25,000	$1,570,099.41
Bellcow	Bellcow 21	710421	$        190,000	$1,762,328.16
Bellcow	Bellcow 21	710421	$        848,958	$1,762,328.16
Bellcow	Bellcow 21	710421	$          19,279	$1,762,328.16
Bellcow	Bellcow 21	710421	$          26,091	$1,762,328.16
Bellcow	Bellcow 21	710421	$        463,180	$1,762,328.16
Bellcow	Bellcow 21	710421	$          35,000	$1,762,328.16
Bellcow	Substation		$          84,330	$359,640.80
Bellcow	Substation		$          15,000	$359,640.80
Bellcow	Substation		$          50,000	$359,640.80
Bellcow	Substation		$        200,000	$359,640.80
Bellcow	Substation		$            1,919	$359,640.80
Bellcow	Substation		$            8,392	$359,640.80

[v-diye-msft]

Hi @Anonymous 

If the above post helps, please kindly mark it as answer to help others find it more quickly. if not, please kindly elaborate more.

[mahoneypat · Accepted solution]

Please try this expression in your calculated column.

Paid Cost =
VAR stationtotal =
    CALCULATE (
        SUM ( Cost[Planned Cost] ),
        ALLEXCEPT ( Cost, Cost[Substation], Cost[Circuit Name] )
    )
VAR substationcost =
    CALCULATE (
        SUM ( Cost[Planned Cost] ),
        ALLEXCEPT ( Cost, Cost[Substation] ),
        Cost[Circuit Name] = "Substation"
    )
VAR circuitcount =
    CALCULATE (
        DISTINCTCOUNT ( Cost[Circuit Name] ),
        ALLEXCEPT ( Cost, Cost[Substation] ),
        Cost[Circuit Name] <> "Substation"
    )
RETURN
    IF (
        Cost[Circuit Name] = "Substation",
        stationtotal,
        stationtotal + substationcost / circuitcount
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat