Measure - Column reference for CALCULATE(SUM(), FILTER() based on values?

[heiavieh]

I'm sure there's a post familiar to this somewhere on the forum, but I could not find it.

I have a table that records accumulated metrics for business units. Here is an example, I'm using Excel just for demonstration purposes.

The "planned" column is used, but not pertinent for my request currently. My intention is to make a measure that sums up the actuals for each business unit as data is added. This is the desired outcome:

Here is what I'm currently getting:

The measures I create are summing up the entire column rather than filtering out by business unit and I'm not sure why. Here's what I've tried so far:

var attempt1 =
    Calculate(SUM(actualVSOP[Actual]),  'actualVSOP'[BusinessUnit]='actualVSOP'[BusinessUnit])
    //this returns the actuals of that specific business unit

var attempt2 = 
    SUMX(All(actualVSOP), actualVSOP[Actual])
    //this returns the sum of ALL actuals in the table

var attempt3 = 
    CALCULATE(SUM(actualVSOP[Actual]), ALL(actualVSOP))
    //also returns sum of all actuals

var attempt4 = 
    Calculate(Sum(actualVSOP[Actual]), Filter(All(actualVSOP), actualVSOP[BusinessUnit] = actualVSOP[BusinessUnit]))
    //same as above

My prediction is that I don't have a proper understanding of column referencing, and that's why I can't grasp this. I don't understand at what point I'm referencing a cell or a row, and then when I'm properly addressing a table, or columns.

I also considered having a calculated table? Would it be a good idea to assign a calculated table to a variable and then performing a filter on that to override the ALL() operator? Any help is much appreciated.

[jgeddes · Accepted solution]

You can try using ALLEXCEPT in your measure. Microsoft Learn - ALLEXCEPT 

BU Actual Total = 
CALCULATE(
    SUM('Table'[Actual]),
    ALLEXCEPT('Table', 'Table'[Business Unit])
)

[fahadqadir3]

@heiavieh Please review the attached screenshot, and use the following measure to sum based on Business Unit.

Measure Fixed = CALCULATE(

    SUM('Table'[value]),

    ALLEXCEPT('Table','Table'[BU])

    )

Regards,

Fahad Qadir

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!