Measure to compared expected with actual

[jonbox]

Hi,

I'm trying to create a measure to count the number of projects that went over their EXPECTED TOTAL SPEND by doing the following

Taking Expected total spend (this stays the same regardless of the month e.g. upgrade project in the below table isn't 8000, it's 2000 but the data just shows it at 2000 per month). Compare the sum of total spend over all months of the project, e.g. upgrade total spend is 300 + 600 + 400 + 800 = 2100 which is OVER the expected total spend of 2000.

Is there a measure that allows me to do this for each project? so that i can count the number over expected spend and number below?

ProjectName	Expected TOTAL Spend	Month	Total Spent
Upgrade	2000	1	300
Upgrade	2000	2	600
Upgrade	2000	3	400
Upgrade	2000	4	800
Expansion	1500	1	700
Expansion	1500	2	700
Expansion	1500	3	50
Refresh	3000	1	1000
Refresh	3000	2	2500

[johnt75]

If you create a measure to calculate the difference, like

Spend difference =
var expectedSpend = MAX('Table'[Expected total spend])
var totalSpent = SUM('Table'[Total Spent])
return expectedSpend - totalSpent

then you could create a couple of measures to show the number over and under budget, e.g.

Num overbudget =
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Table'[Project]), "@val", [Spend difference])
return COUNTROWS( FILTER( summaryTable, [@val] < 0) )

[jonbox]

Hi Johnt,

Do you know how i can resolve the below error? 

Num overbudget = 
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Actual Spend + Firm' [ProjectName]), "@val", [Spend difference])
return COUNTROWS( FILTER( summaryTable, [@val] < 0) )

[johnt75]

My mistake, forgot to include the table as the first parameter to SUMMARIZE

Num overbudget = 
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Actual Spend + Firm',  'Actual Spend + Firm'[ProjectName]), "@val", [Spend difference])
return COUNTROWS( FILTER( summaryTable, [@val] < 0) )

[jonbox]

No worries, worked it out in the end. thanks a lot for the help.

one final thing i'm trying to add is a fitler to only perform this calculation If the project status = closed:

Num underbudget =

var summaryTable = ADDCOLUMNS(SUMMARIZE(FILTER('Actual Spend + Firm', 'Actual Spend + Firm'[ProjectStatus] = "Closed"( 'Actual Spend + Firm','Actual Spend + Firm'[ProjectName]), "@val", [Spend difference])

return COUNTROWS( FILTER( summaryTable, [@val] >= 0) )

Something like this...

[johnt75 · Accepted solution]

Num overbudget = 
var summaryTable = ADDCOLUMNS( SUMMARIZE( 
   FILTER( 'Actual Spend + Firm',  'Actual Spend + Firm'[ProjectStatus] = "Closed"),
   'Actual Spend + Firm'[ProjectName]), "@val", [Spend difference]
)
return COUNTROWS( FILTER( summaryTable, [@val] < 0) )

I think that'll do it