Need Help Correcting a Measure

[RichOB]

I've been trying to get the Net Balance, Net Budget and Net Variance totals which are calculated from 3 separate matrix tables of Income, Staffing Cost, and Expenditure. The 3 Matrix tables has a variety of categories that make up the individual matrix totals.

To get the Total Net Balance, I need the calculation to be:

Total Income Balance - Total Staffing Cost Balance - Total Expenditure Balance = Total Net Balance

Can someone look over the Net Balance measure and correct the syntax, please? Assume that the table and category names are correct.

Net_Income_Balance =

VAR Balance_Income =

    CALCULATE (

        SUM ( 'Table'[Amount1] ),

        FILTER (

            'Table',

            'Table'[Type] = "Balance"

                && ( 'Table'[Category] = "Contract Income"

                || 'Table'[Category] = "Housing Income"

                    ||  'Table'[Category] ="Other Income"

                    ||  'Table'[Category] ="Fundraised Income")

        )

    )

VAR Balance_StaffingCost =

    CALCULATE (

        SUM ( 'Table'[Amount1] ),

        FILTER ( 'Table', 'Table'[Type] = "Balance" && 'Table'[Category] = "Salaries"

            ||'Table'[Category] = "Additional Hours"

            ||'Table'[Category] = "Temporary Staff")

    )

VAR Balance_Expenses =

    CALCULATE (

        SUM ( 'Table'[Amount1] ),

        FILTER (

            'Table',

            'Table'[Type] = "Balance"

                && 'Table'[Category] = "Rent and Rates"

                ||'Table'[Category] = "Total Expenditure"

                ||'Table'[Category] = "Travel"

                ||'Table'[Category] = "Training"

                ||'Table'[Category] = "Recruitment"

                ||'Table'[Category] = "Staff Insurance"

                ||'Table'[Category] = "Sundry Staff Items"

                ||'Table'[Category] = "Postage Printing and Stationary"

                ||'Table'[Category] = "Communications"

                ||'Table'[Category] = "IT Equipment"

                ||'Table'[Category] = "Volunteer Expenses"

                ||'Table'[Category] = "Professional Fees"

                ||'Table'[Category] = "Additional Support Expenditure"

                ||'Table'[Category] = "Rent and Rates"

                ||'Table'[Category] = "Utilities"

                ||'Table'[Category] = "Cleaning"

                ||'Table'[Category] = "Replacement F&F"

                ||'Table'[Category] = "Property Maintenance"

                ||'Table'[Category] = "Equipment Repair and Checks"

                ||'Table'[Category] = "Security"

                ||'Table'[Category] = "Licenses"

                ||'Table'[Category] = "Broadband"

                ||'Table'[Category] = "Vehicle Costs"

                ||'Table'[Category] = "Client Subsidence"

                ||'Table'[Category] = "Depreciation"

                ||'Table'[Category] = "Bad Debts"

                ||'Table'[Category] = "Client Funds"

                ||'Table'[Category] = "Central Overheads"

                ||'Table'[Category] = "IT Systems"

                ||'Table'[Category] = "Finance Charges")

    )

RETURN

    Balance_Income - Balance_StaffingCost - Balance_Expenses

This is how I need it to look

[RichOB]

Hi @Greg_Deckler thanks for getting back to me.

I'm making a financial page and need to show the balance at the bottom of the screenshot. 

I made Balance, Budget, and Variance measures and added them to 3 matrix visuals for Income / Stafing Cost / Expenditure > I then filtered each matrix by the correct category based on what each one needs to display. Shown below:

I need to have a measure (or 2) to add to a 4th Matrix that shows:

1 - the Total Balance (in red) which is the Income Balance total - Staffing Cost Balance Total - Expenditure Balance Total.

2 - the Total Budget (in red) which is the Income Budget total - Staffing Cost Budget Total - Expenditure Budget Total.

3 - the Variance between 1+2 (in blue)

I have no idea how to do this, unfortunately!

Here are the Balance, Budget, and Variance measures in case you need to see them:

Balance = CALCULATE(sum('Table'[Amount1]),'Table'[Type]="Balance")

Budget = CALCULATE(sum('Table'[Amount1]),'Table'[Type]="Budget")

Variance =

VAR _balance = CALCULATE(SUM('Table'[Amount1]),'Table'[Type]="Balance")

VAR _budget = CALCULATE(SUM('Table'[Amount1]),'Table'[Type]="Budget")

RETURN

_balance - _budget

I appreciate any help to get that 4th matrix with the 3 figures, please!

Thanks in advance

Rich

[Greg_Deckler · Accepted solution]

@RichOB OK, so what does the source data look like? Is the Income Balance Total a simple SUM of some column or is there filtering involved? Seems like you could take your 3 VAR statements and just make them individual measures. Then what you want in the card visual would just be a measure that does the following:

Net Income Balance Measure = [Income Balance total] - [Staffing Cost Balance Total] - [Expenditure Balance Total]

Sorry, trying to sort this out. Again, sample data is incredibly helpful in these kinds of questions. Also, you calculations might be a lot cleaner if you did something like this:

Balance_StaffingCost =
    CALCULATE (
        SUM ( 'Table'[Amount1] ),
        FILTER ( 'Table', 'Table'[Type] = "Balance" && 'Table'[Category] IN { "Salaries", "Additional Hours", "Temporary Staff" } )
    )

Personally though I would use this syntax:

Balance_StaffingCost =
    SUMX(
        FILTER ( 'Table', 'Table'[Type] = "Balance" && 'Table'[Category] IN { "Salaries", "Additional Hours", "Temporary Staff" } ),
        [Amount]
    )

[Greg_Deckler]

@RichOB This is pretty much an impossible ask given the information provided. Possibly this??

VAR Balance_Expenses =
    CALCULATE (
        SUM ( 'Table'[Amount1] ),
        FILTER (
            'Table',
            'Table'[Type] = "Balance"
                && ( 'Table'[Category] = "Rent and Rates"
                ||'Table'[Category] = "Total Expenditure"
                ||'Table'[Category] = "Travel"
                ||'Table'[Category] = "Training"
                ||'Table'[Category] = "Recruitment"
                ||'Table'[Category] = "Staff Insurance"
                ||'Table'[Category] = "Sundry Staff Items"
                ||'Table'[Category] = "Postage Printing and Stationary"
                ||'Table'[Category] = "Communications"
                ||'Table'[Category] = "IT Equipment"
                ||'Table'[Category] = "Volunteer Expenses"
                ||'Table'[Category] = "Professional Fees"
                ||'Table'[Category] = "Additional Support Expenditure"
                ||'Table'[Category] = "Rent and Rates"
                ||'Table'[Category] = "Utilities"
                ||'Table'[Category] = "Cleaning"
                ||'Table'[Category] = "Replacement F&F"
                ||'Table'[Category] = "Property Maintenance"
                ||'Table'[Category] = "Equipment Repair and Checks"
                ||'Table'[Category] = "Security"
                ||'Table'[Category] = "Licenses"
                ||'Table'[Category] = "Broadband"
                ||'Table'[Category] = "Vehicle Costs"
                ||'Table'[Category] = "Client Subsidence"
                ||'Table'[Category] = "Depreciation"
                ||'Table'[Category] = "Bad Debts"
                ||'Table'[Category] = "Client Funds"
                ||'Table'[Category] = "Central Overheads"
                ||'Table'[Category] = "IT Systems"
                ||'Table'[Category] = "Finance Charges" )
          )
    )