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]
    )
RichOB's avatar
RichOB
Post Partisan
2 years ago

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's avatar
    Greg_Deckler
    Community Champion
    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]
    )