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 ...
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
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