Running total and average of measure for FTE

[Anonymous]

Hello,

Problem: I can't figure out how to create a running total or average over a measure. 

Context:

I have a measure which calculates the actual FTE, based on daily hours worked, divided by a set constant, corrected for the number of employees in the filter context. This gives me the correct FTE which I display per month in a table.
I have a separate date table, connected to the fact table.

Measure: 

FTE SUM:=CALCULATE (SUM ( Fct_DD[hours] ) / CALCULATE (SUM ( Fct_DD[FTE_constant] ) / DISTINCTCOUNT ( Fct_DD[ID_Employee] )))

Now I want to show a running total over the months and an average over this running total. So when the table shows:

I want the RT and AVG to look like

I tried several approaches but I can't seem to get the right formulas. E.g. using basic TOTALYTD shows wrong numbers, while TOTALMTD just shows the same number as FTE.

Also: SUMX(FILTER(ALLSELECTED(Dim_REF_Date[MonthNr]);Dim_REF_Date[MonthNr]<=MAX(Dim_REF_Date[MonthNr]));[FTE SUM])
shows the same number as the FTE column.

I hope anyone can help me in the right direction!

[v-juanli-msft]

Hi @Anonymous 

Create measures

RT = CALCULATE(SUM(Table3[FTE]),FILTER(ALLEXCEPT(Table3,Table3[department]),Table3[date]<=MAX('Table3'[date])))

AVG = CALCULATE(AVERAGE(Table3[FTE]),FILTER(ALLEXCEPT(Table3,Table3[department]),Table3[date]<=MAX('Table3'[date])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi Maggie,

Thank you for the reply.

I tried out the formula, but there were several problems.
The 'Table3[FTE]' is not a table, but a measure. I believe SUM requires a column? Also, the date within the fact table is an ID, linking to a date table. I tried some variations using 'SUMX(VALUES(Date[date]); [FTE measure])'. But this also did not work.

Then I found another similar formula on a forum which worked for my situation:

(RT = measure3 in this example, while measure 1 would by my 'FTE SUM' measure)

Measure3 := CALCULATE( 
    SUMX( VALUES(Calendar[Month]), [measure1] )
    , FILTER( 
         ALL(Calendar)
        , Calendar[Date] <= MAX(Calendar[Date])
        && Calendar[Year] = MAX(Calendar[Year])
        )
    )

  For the AVG, I just divided this formula by 'MAX(Date[MonthNr])'

Thanks all!

[Anonymous]

The power of DAX, I am amazing this is really cool.

Hi From Ecuador

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[d_gosbell]

ALLSELECTED will return the current month within the filter context, have you tried using ALL instead?

SUMX(FILTER(ALL(Dim_REF_Date[MonthNr]);Dim_REF_Date[MonthNr]<=MAX(Dim_REF_Date[MonthNr]));[FTE SUM])

[Anonymous]

Thank you for your reply.

ALL seems to give the exact same number as ALLSELECTED, which is the monthly FTE SUM value. 

I tried using BK_Date instead of MonthNr, which at least shows some sort of Running total over the months, but this gives inconsistent numbers, such as different outcomes for the same FTE SUM value.