Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Running Total on two sets of Data

Hi, im new to Power Bi .

Im trying to generate an S-Curve for resources.

Budget VS Actual using a stacked bar with line for cumulative Units a running total for each.

Im trying to get a running total for Budgeted Units  VALUES and a second one for Actual Units VALUES.

 

 

With an end result with both sets of values on the chart.

 

 

Thanks in advance 🙂

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous

     

    You can create two measures in the model to calculate the running totals for actual and budget values separately. Add both to Line y-axis on the visual. 

    Running Total of Actual =
CALCULATE (
    SUM ( SampleTable[Value] ),
    ALLSELECTED ( SampleTable[Date] ),
    SampleTable[Date] <= MAX ( SampleTable[Date] ),
    SampleTable[Spreadsheet Field] = "Actual Units"
)

    Running Total of Budget =
CALCULATE (
    SUM ( SampleTable[Value] ),
    ALLSELECTED ( SampleTable[Date] ),
    SampleTable[Date] <= MAX ( SampleTable[Date] ),
    SampleTable[Spreadsheet Field] = "Budgeted Units"
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Use this measure for actual units. 

    Running Total of Actual = 
VAR lastActualDate =
    CALCULATE (
        MAX ( SampleTable[Date] ),
        ALLSELECTED ( SampleTable[Date] ),
        SampleTable[Spreadsheet Field] = "Actual Units"
    )
RETURN
    IF (
        MAX ( SampleTable[Date] ) <= lastActualDate,
        CALCULATE (
            SUM ( SampleTable[Value] ),
            ALLSELECTED ( SampleTable[Date] ),
            SampleTable[Date] <= MAX ( SampleTable[Date] ),
            SampleTable[Spreadsheet Field] = "Actual Units"
        ),
        BLANK ()
    )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

7 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous

     

    You can create two measures in the model to calculate the running totals for actual and budget values separately. Add both to Line y-axis on the visual. 

    Running Total of Actual =
CALCULATE (
    SUM ( SampleTable[Value] ),
    ALLSELECTED ( SampleTable[Date] ),
    SampleTable[Date] <= MAX ( SampleTable[Date] ),
    SampleTable[Spreadsheet Field] = "Actual Units"
)

    Running Total of Budget =
CALCULATE (
    SUM ( SampleTable[Value] ),
    ALLSELECTED ( SampleTable[Date] ),
    SampleTable[Date] <= MAX ( SampleTable[Date] ),
    SampleTable[Spreadsheet Field] = "Budgeted Units"
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much :).

       

      Is there a way i can modify the code to stop the code from running for the actual units up to a certain date? so i can prevent the to prevent the flat line afterwards. To Try and show a tracking line Budget VS Actual?

       

       

       

       

       

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        Use this measure for actual units. 

        Running Total of Actual = 
VAR lastActualDate =
    CALCULATE (
        MAX ( SampleTable[Date] ),
        ALLSELECTED ( SampleTable[Date] ),
        SampleTable[Spreadsheet Field] = "Actual Units"
    )
RETURN
    IF (
        MAX ( SampleTable[Date] ) <= lastActualDate,
        CALCULATE (
            SUM ( SampleTable[Value] ),
            ALLSELECTED ( SampleTable[Date] ),
            SampleTable[Date] <= MAX ( SampleTable[Date] ),
            SampleTable[Spreadsheet Field] = "Actual Units"
        ),
        BLANK ()
    )

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Is there a way of adapting the expression to have it as a percentage of total units? 

     

    Planned % complete Vs Actual % of complete.

     

     

     

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi Anonymous 

      What should be the total (denominator) on every date? 

      Planned % of complete = (?) / (?)

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

         

        Hope this helps.

         

        Is it possible as before to stop the Actual % Running only to the max date to prevent the flat cumulative line?

         

        Thanks in advance ,

         

        Really appreciate it.