Filter Calculation - MUST BE NEW COLUMN

[enswitzer]

I have a set of data that I am going to want to filter by (ultimately going to put this calculation into an if statement to determine if it's overbudget and want to be able to slice by the red/yellow/green output).

The data looks like this, and I have created a formula (overbudget) that right now just sums everything by the project ID, however I want use it to say the following for each project ID:

If (Sum of Expenditure (type = target) >= sum of Expenditure (type = trend), "On Plan", "Overbudget")

Overbudget (CURRENTLY) = Calculate(Sum('PP - Project Status: Spend Chart'[Value]), ALLEXCEPT('PP - Project Status: Spend Chart', 'PP - Project Status: Spend Chart'[Project IDId]))

I seem to have no issues doing this as measures - but get stuck trying to do it as a new column. 

[enswitzer]

@Greg_Deckler 

Looking to create the Overbudget column just not as a measure. 

Current measure formulas:

Total Budget =

CALCULATE (

[Total Value],

'PP - Project Status: Spend Chart'[Expenditure Type] = "Target"

)

Total Trend Spend =

CALCULATE (

[Total Value],

'PP - Project Status: Spend Chart'[Expenditure Type] = "Actual"

)

+ CALCULATE (

[Total Value],

'PP - Project Status: Spend Chart'[Expenditure Type] = "Trend"

)

Overbudget Measure = if([Total Budget]>=[Total Trend Spend], "On Plan", "Over Budget")

[v-kelly-msft]

I don'@enswitzer,

You can create a measure as follows:

Measure = 
var _totalbudget=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Project ID]=MAX('Table'[Project ID])&&'Table'[Expenditure Type]="Target"))
var _totaltrendspend=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Project ID]=MAX('Table'[Project ID])&&'Table'[Expenditure Type]="Trend"))
Return
IF(_totalbudget>=_totaltrendspend,"On Plan", "Overbudget")

And you'll see:

For the related .pbix file, see attachment pls.

Saludos
Kelly

Have I answered your question? Mark my position as a solution!

[Greg_Deckler]

@enswitzer So given the data above, what would this new column be for each row?