Forum Discussion

AnAnalyst's avatar
AnAnalyst
Icon for Helper III rankHelper III
6 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]))

What I need to do is only include the items that have a value > 0 at the context of Year and Project (PROJECT[PROJECT]). 

 

Any tips are very much appreciated. 

 

Thank you

Need Help

2 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 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
      6 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.