Power BI - using Switch to calculate subtotals in an income statement

Question

I have a fixed format table and am trying to use the switch formula to calculate different rows together. I have allocated a 1,2,3 to a particular row with 1 - sum base data, 2 - sum total administrative expense, 3 - running total.

Financial Value = 
  var SelectedCalcType = SELECTEDVALUE('Management accounts structure'[calctype])
  RETURN SWITCH(SelectedCalcType, 
    1,[Total Amount],
    2,[Total administrative expenses],
    3,[total Amount Running Total],
    BLANK()
)

[Total Amount] is:

Total Amount = SUM('Glentry query'[Debit/Credit Amount])

[Total administrative expenses]

Total administrative expenses = [Staff related costs]+[Other administrative costs]

[Staff related costs] is

Staff related costs = CALCULATE([Total Amount],'Account mapping query'[ManagementHeader]="Staff related costs")

and [Other administrative costs] is the same but with Other administrative costs as the filter.

Now this works for everything but 2 as this is a subtotal of the 2 previous rows above it so in a way sits outside of 3. Does anyone have any ideas as to how to show subtotals in power bi?

This is the management accounts structure:

This is my data relationships:

Updated data relationships:

and what the matrix table currently looks like:

Answer 1

Looking at your data model, you will need to remove the Management accounts structure context from the calculation so that Account mapping query isn't filtered.

See if this helps:

Total administrative expenses = 
  CALCULATE(
    [Staff related costs] + [Other administrative costs],
    REMOVEFILTERS('Management accounts structure')
  )

Should Other administrative costs be calctype 1 or 2? In your annotated screen grab you have it as 1, and in the second it is showing 2. I suspect it should be 1.

---

Supplimental
As discussed in the comments, you could alternatively remove the relationship between Management accounts structure and Account mapping query and do the required filtering in the Financial Value measure for each row calculation.

Your Financial Value measure could look similar to:

Financial Value = 
  var selectedCalcType = SELECTEDVALUE('Management accounts structure'[calctype])
  var selectedLineItem = SELECTEDVALUE('Management accounts structure'[Line Item ID])

  RETURN SWITCH(selectedCalcType, 

    // 1 = we'll use the line item for filtering where previously the relationship was used
    1,
      [Total Amount]('Account mapping query'[ManagementHeader] = selectedLineItem),
    
    2, 
      var src = [Total Amount]('Account mapping query'[ManagementHeader] = "Staff related costs")
      var oac = [Total Amount]('Account mapping query'[ManagementHeader] = "Other administrative costs")
      return src + oac,

    // update this parts as needed
    3,
      [total Amount Running Total]

  ) // end Switch