Complicated Sum formula of Object to NOT include

[Anonymous]

I am doing projections for contractual services and have the long following code; however my other column is not correct. I want to filter by all objects that are NOT the ones listed above. So basically how can i create a VAR other calculations that is not electricity, natural gas, etc.? There are many objects in this table so it just is not coded as Other. Contractual = VAR ELECTRICITY = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "Electricity" ) VAR NATURALGAS = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "NATURAL GAS" ) VAR WATER = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "WATER" ) VAR SEWER = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "SEWER SERVICE" ) VAR STORM = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "STORM WATER" ) VAR INSURANCEVEHICLES = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "INSURANCE VEHICLES" ) VAR PROPERTYINSURANCE = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object] = "INSURANCE PROPERTY" ) VAR OTHER = CALCULATE ( SUM ( 'GL298A'[BUDGET-DTL] ), 'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object Category] = "Contractual", 'Accounts'[Object] = "Special Assessments" ) VAR ELECTRICITY2023 = -1.06 * ELECTRICITY VAR NATURALGAS2023 = -1.06 * NATURALGAS VAR WATER2023 = -1.15 * WATER VAR SEWER2023 = -1.04 * SEWER VAR STORM2023 = -1.15 * STORM VAR INSURANCEVEHICLES2023 = -1.07 * INSURANCEVEHICLES VAR PROPERTYINSURANCE2023 = -1.05 * PROPERTYINSURANCE VAR OTHER2023 = -1.00 * OTHER RETURN ELECTRICITY2023 + NATURALGAS2023 + WATER2023 + SEWER2023 + STORM2023 + INSURANCEVEHICLES2023 + PROPERTYINSURANCE2023 + OTHER2023

[v-yanjiang-msft]

Hi @Anonymous ,

Has @johnt75 's solution solved your problem?

Best Regards,
Community Support Team _ kalyj

[johnt75 · Accepted solution]

You'd have to put all the other object values into a NOT ... IN construct, so in the OTHER variable replace the Accounts[Object] line with

NOT ( 'Accounts'[Object] IN { "INSURANCE PROPERTY", "STORM WATER" .... } )

[Anonymous]

Contractual = 
VAR ELECTRICITY =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "Electricity"
    )
VAR NATURALGAS =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "NATURAL GAS"
    )
VAR WATER =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "WATER"
    )
VAR SEWER =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "SEWER SERVICE"
    )
VAR STORM =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "STORM WATER"
    )                                                                                                                                                                                                         VAR INSURANCEVEHICLES =                                                                                                                                                                                            CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "INSURANCE VEHICLES"
    )
VAR PROPERTYINSURANCE =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object] = "INSURANCE PROPERTY"
    )
VAR OTHER =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022, 'Accounts'[Object Category] = "Contractual",
        'Accounts'[Object] = "Special Assessments"
    )
VAR ELECTRICITY2023     = -1.06 * ELECTRICITY
VAR NATURALGAS2023   = -1.06 * NATURALGAS
VAR WATER2023   = -1.15 * WATER 
VAR SEWER2023 = -1.04 * SEWER 
VAR STORM2023     = -1.15 * STORM
VAR INSURANCEVEHICLES2023   = -1.07 * INSURANCEVEHICLES
VAR PROPERTYINSURANCE2023   = -1.05 * PROPERTYINSURANCE 
VAR OTHER2023 = -1.00 * OTHER 
RETURN
	ELECTRICITY2023 + NATURALGAS2023 + WATER2023 + SEWER2023 + STORM2023 + INSURANCEVEHICLES2023 + PROPERTYINSURANCE2023 + OTHER2023