Forum Discussion

byr10112's avatar
byr10112
Microsoft Employee
3 years ago

Measure with Row Level Calculations

Hello, I am trying to create a measure that will show me the full year forecast. There are 3 variables that go into the calculation. The measure: Forecast = SUM(Revenue[Actuals]) + Sum(Revenue[Sche...
Need Help
jgeddes's avatar
jgeddes
Super User
3 years ago

You can try the following measure (it is ugly, but it seems to work).

Forecast = 
var _vtable =
SUMMARIZE(
    accountTable,
    accountTable[Account ID],
    "_Forecast", IF(OR(ISBLANK(SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue])), SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue])=0), SELECTEDVALUE(wonPipelineTable[Won Pipeline]), SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue]))
)
Return
SUMX(_vtable, [_Forecast])

I ended up with the result

 

  • byr10112's avatar
    byr10112
    Microsoft Employee
    3 years ago

    Its not giving me correct results. Not sure what is broken, but this is giving me a significantly lower number.