Running Total on two sets of Data

[Anonymous]

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 🙂

[v-jingzhang · Accepted solution]

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]

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]

Hi @Anonymous 

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

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

[Anonymous]

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.

[v-jingzhang · Accepted solution]

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]

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 · Accepted solution]

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]

Thank You so much 🙂