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 to...
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.

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

      Thank You so much 🙂