Average measure (over time)

[Luuk_J]

Hi everyone,

I have a question regarding making an average over time measure in Power BI.

Ultimately, I need to average the number of FTE (full-time equivalent) during a certain period in time, combined with some other dimensions.

For example, I have employee number 1 who worked for 1.00 FTE in month 1,2 and 3.
On top of that, I have employee number 2 who also worked for 1.00 FTE, but only in month 3.
That means that there is no data for employee number 2 in month 1 and 2.

My problem is as follows:

When I select month 1,2 and 3, I need to get a average of 1.33 FTE from those two employees over that period.
Employee number 1 needs to count as (1+1+1) / 3 months = 1 FTE on average
Employee number 2 needs to count as (0+0+1) / 3 months = 0.33 FTE on average
Total of those two employees has to be 1.33 FTE.

When I use the following measure:

AVERAGE('Fact'[FTE])

It doesn't show me the output I want, and gives me 1.00 FTE on both row-level and total.

When I use the following measure:

SUM('Fact'[FTE])/DISTINCTCOUNT('Fact'[Datekey])

It partially shows me the output I want. The totals are OK (1.33 FTE) but on row level, it still shows me 1.00 FTE per row.

When I use the following measure:

AVG FTE Divide =
VAR avs =
SUM ( 'Fact'[FTE] )
VAR dis =
CALCULATE ( DISTINCTCOUNT ( 'Fact'[Datekey]), ALLSELECTED( 'Fact'))
RETURN
DIVIDE(avs,dis)

It shows me exactly what I want, on both row-level and totals. As shown as in the screenshot below:

However, the moment I add any column from the time dimension to the table, the row-level output of that measure is not correct anymore, as shown in the screenshot below:

I understand why that happens, since I divide every row by the same value.

But is there any way to prevent that from happening?

Perhaps by combining my second (AVG FTE Distinct) and third measure into one column?
I'm trying to prevent making two different columns (which I have now), since I my data model gets used by other end users and I want it to be as idiot proof as possible.

Due to the confidentiality of the original data, I have created and uploaded a sample file which has the measures and data of this post in it. The sample file can be downloaded through the following wetransfer link:

https://we.tl/t-ddA5uv0aNK

Regarding the solution: inserting dummy records into the fact is not an option; since the real data set fact has millions of records and over 10 different dimensions.

Any help is much appriciated, thanks in advance.

[v-alq-msft]

Hi, @Luuk_J 

You may modify the measure as below.

AVG FTE divide = 
VAR avs = 
CALCULATE(
    SUM ( 'Fact'[FTE] ),
    ALLEXCEPT('Fact','Fact'[Employee])
)
VAR dis =
  CALCULATE ( DISTINCTCOUNT ( 'Fact'[Datekey]), ALLSELECTED(  'Fact'))
  RETURN
  DIVIDE(avs,dis)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Luuk_J]

Hi Allan, Thank you very much for your suggestion/answer. Although your solution works in the sample file, it unfortunately does not in the original PBIX file I am working with. It may have to do with the bigger number of dimensions I am working with in the original file, but I have to look into it. I am planning to do that later this week and I will post an update afterwards. Kind regards, Luuk

[mahoneypat]

Here is a measure expression that works.  Note that it has nested iterator so performance may be poor at large scale. 

New Avg FTE =
SUMX (
    DISTINCT ( 'Fact'[Employee] ),
    CALCULATE (
        AVERAGEX (
            DISTINCT ( 'Date dimension'[Datekey] ),
            CALCULATE (
                AVERAGE ( 'Fact'[FTE] )
            ) + 0
        )
    )
)

Regards,

Pat

[Luuk_J]

Hi Pat,

Thank you very much for your suggestion/answer.

Although your solution works in the sample file, it unfortunately does not in the original PBIX file I am working with.
It may have to do with the bigger number of dimensions I am working with in the original file, but I have to look into it.
I am planning to do that later this week and I will post an update afterwards.

Kind regards,

Luuk