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

 but for Bellcow we have 2 Circuit and substation 

so the Paid Cost calculation is sum of Planned cost + Sum of Substation/count of Circuit

Note the Paid Cost calculation for Substation will be just sum of planned cost 

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
Bellcow	Bellcow 21	$19,279	1762328.5
Bellcow	Bellcow 21	$26,091	1762328.5
Bellcow	Bellcow 21	$35,000	1762328.5
Bellcow	Bellcow 21	$190,000	1762328.5
Bellcow	Bellcow 21	$463,180	1762328.5
Bellcow	Bellcow 21	$848,958	1762328.5
Bellcow	Bellcow 50	$12,476	1755525.5
Bellcow	Bellcow 50	$25,000	1755525.5
Bellcow	Bellcow 50	$27,383	1755525.5
Bellcow	Bellcow 50	$124,500	1755525.5
Bellcow	Bellcow 50	$298,180	1755525.5
Bellcow	Bellcow 50	$902,740	1755525.5
Bellcow	Substation	$1,919	$359,641
Bellcow	Substation	$8,392	$359,641
Bellcow	Substation	$15,000	$359,641
Bellcow	Substation	$50,000	$359,641
Bellcow	Substation	$84,330	$359,641
Bellcow	Substation	$200,000	$359,641

[v-juanli-msft]

Hi @Anonymous 

Creat a column 

Column =
IF (
    'Table'[Circuit Name] = "Substation",
    CALCULATE (
        SUM ( 'Table'[Planned Cost] ),
        FILTER (
            'Table',
            'Table'[Substation] = EARLIER ( 'Table'[Substation] )
                && 'Table'[Circuit Name] = "Substation"
        )
    )
        / CALCULATE (
            DISTINCTCOUNT ( 'Table'[Circuit Name] ),
            FILTER (
                'Table',
                'Table'[Substation] = EARLIER ( 'Table'[Substation] )
                    && 'Table'[Circuit Name] = "Substation"
            )
        ),
    CALCULATE (
        SUM ( 'Table'[Planned Cost] ),
        FILTER ( 'Table', 'Table'[Substation] = EARLIER ( 'Table'[Substation] ) )
    )
        / CALCULATE (
            DISTINCTCOUNT ( 'Table'[Circuit Name] ),
            FILTER ( 'Table', 'Table'[Substation] = EARLIER ( 'Table'[Substation] ) )
        )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

HI there 

the Calculation went somewhere wrong the paid cost value will be 

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-juanli-msft]

Hi @Anonymous 

final =
VAR t1 =
    CALCULATE (
        SUM ( 'Table 6'[Planned Cost] ),
        FILTER (
            'Table 6',
            'Table 6'[Substation] = EARLIER ( 'Table 6'[Substation] )
                && 'Table 6'[Circuit Name] = EARLIER ( 'Table 6'[Circuit Name] )
        )
    )
VAR c1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table 6'[Circuit Name] ),
        FILTER (
            'Table 6',
            'Table 6'[Substation] = EARLIER ( 'Table 6'[Substation] )
                && 'Table 6'[Circuit Name] <> "Substation"
        )
    )
VAR t2 =
    CALCULATE (
        SUM ( 'Table 6'[Planned Cost] ),
        FILTER (
            'Table 6',
            'Table 6'[Substation] = EARLIER ( 'Table 6'[Substation] )
                && 'Table 6'[Circuit Name] = "Substation"
        )
    )
RETURN
    t1 + t2 / c1

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

How did you calculate the figure of 1762328.5?  Show it clearly.

[Anonymous]

Hi there 

the calculation 

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)  Note the 2 is the count of Circuit for Bellcow it will be different for each Circuit

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

Bellcow 50	710450	$ 1,570,099.41
Bellcow 21	710421	$1,762,328.16
Substation		$359,641