Forum Discussion

AnAnalyst's avatar
AnAnalyst
Icon for Helper III rankHelper III
7 years ago

CALCULATE CONTEXT Help

Hello,   I have this formula that gives me the correct total for the entire record set:   CALCULATE(SUM(PROJECT[Forecast $]) - sum(PROJECT[Plan $]), ALL('PROJECT'[EoM]), YEAR('PROJECT'[EoM])) W...
Need Help
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
7 years ago

Hi AnAnalyst 

try 

CALCULATE(
SUM(PROJECT[Forecast $]) - sum(PROJECT[Plan $]), 
FILTER(ALL('PROJECT'[EoM]), PROJECT'[Value]>0)
)
  • AnAnalyst's avatar
    AnAnalyst
    Icon for Helper III rankHelper III
    7 years ago

    This works for the aggregate level but not for the granularity I need. I am currently displaying a matrix like below with visual level filters to remove the KPI Measure = 0. I need the same total as the matrix. 

     

    I should add there is more data in the underlying table than what i shown here (versions, months, etc). So, I need to specify what granularity to do the calculation. 

     

    For each year, and project, filter our records where YTD Variance > 0 and FY Variance < 0

     

    Sorry, I realize this expands on my original question a bit.