Running total of a measure

[Ollie B]

Hello!

I am attempting to compare budgeted vs. actual costs per full-time employee using PowerPivot, and running into some challenges. I want to display the running total in a line chart, and for the line to stop when there is no more data.

A demonstration, using total costs (not per FTE)

• I do not want this (default behavior for the built-in PivotTable Running total): Bad line
• I want this (simple measure): Nice line

The code for the measure that displays the correct result is:

Cumulative amount:=IF(ISBLANK([Sum of Amount]);BLANK();TOTALYTD(SUM(Transactions[Amount]);DimDate[Date]))

All good, right? Well, not exactly. To calculate the amount per FTE, I'm using another simple measure:

Amount per FTE:=SUM(Transactions[Amount]) / SUM(Employees[FTE])

Because SUM only takes a reference to a column, I am unable to re-use the code from Cumulative amount, and now I am stuck. I've tried all sorts of TOTALYTD, CALCULATE and SUMX combinations, but I am either getting non-cumulative and/or otherwise incorrect values. Had SUM([Measure]) worked, I could simply have done:

Cumulative amount per FTE:=IF(ISBLANK([Sum of Amount]);BLANK();TOTALYTD(SUM([Amount per FTE]);DimDate[Date]))

- but no such luck.

For example, the following code produces incorrect values that are also not cumulative, as seen here:

Cumulative APF:=IF(ISBLANK([Sum av Amount]);BLANK();TOTALYTD(SUM(Transactions[Amount])/SUM(Employees[FTE]); DimDate[Date]))

Using the built-in PivotTable Running total correctly displays the cumulative values, but fills blank cells with the last value.

The data model is still being worked on, but should be pretty straight forward: two fact tables (Transactions and Employees) and a set of dimension tables, including one for dates.

I'm (almost) sure this is actually a no-brainer, but my brain has met its match. Does anyone know what to do here?

Thanks!

 

[gazpage]

CALCULATE (
    SUMX ( 
        VALUES ( DimDate[Month] ),
        SUM ( Amount ) / SUM ( Employees )
    ),
    DATESYTD ( DimDate )
)

and wrap in your ISBLANK() part from your first measure to kill off the trailing values.

 

[Ollie B]

Hurray, that worked! The values were a little off (rounding peculiarity perhaps?), similar to what was seen in my wholly incorrect Cumulative APF example, but replacing

SUM ( Amount ) / SUM ( Employees )

with

[Amount per FTE]

fixed it. Thank you!

 

[gazpage]

Ugh, yes. Putting a CALCULATE around that part would have worked as well.

 

[Ollie B]

It seems I celebrated too quickly. I'm having a strange issue where not all historical data is shown. I modified the Scenario part of the data model, from "Budget, Actual" to "2016A(ctual), 2016B(udget), 2017A, 2017B" (and so on), to make it possible to select (via a slicer) a single year's budget (like the 2017 budget) but multiple years of actual data (with separate Year and Scenario slicers, it was all or nothing). This works fine when displaying Sum of Amount (or FTEs), but using the measure to calculate Cumulative Amount per FTE from above only shows values from 2017. The Cumulative amount measure from earlier also only displays 2017 numbers.

To make it even weirder, including Year as a column label makes the historical data appear again, but the mystery scenarios are shown as empty in the slicer. I would prefer to only display the Scenario in either case, as including both makes for some ugly diagrams.

Any ideas on what is going on?

 

[gazpage]

Hard to understand from your limited description of your data model.

 

[Ollie B]

I figured it out. Well, not what was going on, but how to fix it. I replaced the DATESYTD-call with

FILTER(ALL(DimDate[Month]);DimDate[Month] <= MAX(DimDate[Month]))

and it seems to be working again.

 

[gazpage]

Well, that doesn't seem to be a YTD calculation as it doesn't restrict you to the current year.