Forum Discussion

RichOB's avatar
RichOB
Post Partisan
2 years ago
Solved

Need Help Correcting a Measure

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 var...
General Comment
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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's avatar
Greg_Deckler
Community Champion
2 years ago

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" )
          )
    )