Forum Discussion
jonbox
Helper II
Helper IIMeasure to compared expected with actual
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...
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
johnt75
Super User
Super UserIf 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 - totalSpentthen 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) )
jonboxHelper II
Helper IIHi 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
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