Forum Discussion
Calculation by Category
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 |
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
2 Replies
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
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.
