Forum Discussion

jonbox's avatar
jonbox
Icon for Helper II rankHelper II
4 years ago
Solved

Measure 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...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago
    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

jonbox's avatar
jonbox
Icon for Helper II rankHelper II
4 years ago

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's avatar
johnt75
Icon for Super User rankSuper User
4 years ago
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