Forum Discussion
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 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 |
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
5 Replies
johnt75Super User
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 - 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) )
jonboxHi 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) )