Forum Discussion
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 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 =
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] )
3 Replies
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" ) ) )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 - _budgetI appreciate any help to get that 4th matrix with the 3 figures, please!
Thanks in advance
Rich
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] )
