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
- mahoneypat
Microsoft Employee
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
- v-diye-msft
Community Support
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.
