Forum Discussion

ftorres's avatar
ftorres
Icon for Helper I rankHelper I
7 years ago
Solved

Cummulative with measure not working

I'm trying to create a cummulative calcule but it's not working.

I've created 1 measure with Earned Value which is Earned Value = ([Planned Value BAC] * [Total of % Complete]) / 100

and then I created the

EV cummulative = CALCULATE([Earned Value], FILTER(ALLSELECTED(Budget),Budget[account_period] <= MAX(Budget[account_period])))

the result is diplayed below and the total is right, but it doesn't follow the sum. I've created also BAC cummulative and Actual cost cummulative and both works.

 

Need Help
  • ftorres's avatar
    ftorres
    7 years ago

    I have to create a column Earned Value instead of a measure.

    then the code below worked.

    EV cummulative = CALCULATE([Earned Value], FILTER(ALLSELECTED(Budget),Budget[account_period] <= MAX(Budget[account_period])))

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Tough to understand what is going on given the information provided. I take it that [Planned Value BAC] and [Total % Complete] are also measures? What are their formulas? What is the value of [Total % Complete] in the rows show below? What are your formulas for the cummulative totals that work?

     

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    • ftorres's avatar
      ftorres
      Icon for Helper I rankHelper I
      7 years ago

      Hi Greg,

      Both are measures with SUM function.

      Planned Value BAC = IF(SUM(Budget[budget_rev])<>0, SUM(Budget[budget_rev]), BLANK())

      Total of % Complete = SUM(Budget[% Complete])

       

      This measure below works

      BAC cummulative = CALCULATE([Planned Value BAC],FILTER(ALLSELECTED(Budget),Budget[account_period] <= MAX(Budget[account_period])))

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi ftorres,

         

        You formula seems well, can you please share a pbix file with some sample data for test?

         

        In addition, you can also try to use following measure to calculate cumulative based on other measure:

        Commulative =
VAR currPeriod =
    MAX ( Budget[account_period] )
VAR summary =
    ADDCOLUMNS ( ALLSELECTED ( Budget ), "EV", [Earned Value] )
RETURN
    SUMX ( FILTER ( summarize, Budget[account_period] <= currPeriod ), [EV] )

         

        Regards,
        Xiaoxin Sheng

  • ftorres's avatar
    ftorres
    Icon for Helper I rankHelper I
    7 years ago

    I have to create a column Earned Value instead of a measure.

    then the code below worked.

    EV cummulative = CALCULATE([Earned Value], FILTER(ALLSELECTED(Budget),Budget[account_period] <= MAX(Budget[account_period])))