Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

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.