Forum Discussion

jamiefisher's avatar
jamiefisher
Helper I
1 year ago

Matrix Total Calculation

I have the following Matrix

The columns 1 budget 2 comm 3 actual are all splits of one field.

My table has rows of values and types, I am using the types to pull into these three categories.

I'd like to add a column total of budget - comm - actual to show a variance.

I can't define a formula as all the values are held in the same field and the value type is all i have to differentiate between them. Can anyone help please

@

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    jamiefisher Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • jamiefisher's avatar
      jamiefisher
      Helper I
      1 year ago

      Thanks for coming back - below is the text 

       

      Row Labels1 Budget2 Comm3 ActualGrand Total
      50100-CORPORATE£650,743.80£0.00£245,539.94£896,283.74
      50300-BUSINESS SERVICES£740,464.80£10,556.01£583,606.00£1,334,626.81
      Grand Total£1,391,208.60£10,556.01£829,145.94£2,230,910.55

       

      I want a calculated total of 1 - 2 -3 

       

      However. 1,2 & 3 are calcuated from table like this

      50300 Value 100 type 1 

      50100 Value 200 type 2

      50300 Value 100 type 2

      So I have a new column setup to say is type is 1 budget if type is 2 actual etc

       

      I hope this explains properly

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        1 year ago

        jamiefisher OK, so are 1 Budget, 2 Comm, 3 Actual are those measures or are those values in a column?

         

        I mean, in general you should be able to create a measure like the following:

        Measure = 
  VAR __1 = SUMX( FILTER( 'Table'[Column] = "1 Budget" ), [Value] )
  VAR __2 = SUMX( FILTER( 'Table'[Column] = "1 Budget" ), [Value] )
  VAR __3 = SUMX( FILTER( 'Table'[Column] = "1 Budget" ), [Value] )
  VAR __Result = __1 - __2 - __3
RETURN
  __Result

        Note, I am making assumptions about your data model because I don't know what it actually looks like.