Forum Discussion

Luuk_J's avatar
Luuk_J
Frequent Visitor
5 years ago

Average measure (over time)

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 tim...
Need Help
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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's avatar
    Luuk_J
    Frequent Visitor
    5 years ago

    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