Average of a measure

[Jodallen123]

Hi,

I always seem to struggle with average measures in PBI. I have a measure that is bascially measure 1 / measure 2, a pretty straight forward measure. Now I would like to calculate an average of this measure that gives me a correct value regardless if I filter the past week or the past year etc. 

I have a measure like this:

test1234 =

AVERAGEX(

    VALUES('Master Time Table'[Week of Year]),  

    [Testmeasure]    

)

Visualizing this in a table by itself or with week of year gives me the expected value that I want. However, if I put it in a matrix visual with yearmonth, week of year on the rows, I get incorrect values. 

Correct:

Incorrect:

How can I alter the measure to give me the correct value reagardless of which period I am filtering? 

Thanks in advance! 

[tamerj1 · Accepted solution]

Hi @Jodallen123 

Please try

Test1234 =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Master Time Table',
            'Master Time Table'[YearMonthNo],
            'Master Time Table'[Week of Year]
        ),
        [Testmeasure]
    )
)

[v-sgandrathi · Accepted solution]

Hi @Jodallen123,

Thank you for your follow-up!

I understand the challenge you're facing with needing to explicitly mention the dimensions in the SUMMARIZE function, which can lead to incorrect values when adding new dimensions, like Quarter, to the matrix visual.

To make the measure more dynamic and adaptable to changing dimensions in your visual (such as adding Quarter, Month, etc.), you can leverage the ALLSELECTED() function, which will consider all the current selections from the slicers and filters while allowing flexibility in the dimensions shown in the visual.

Try this Dax Measure:

 test1234 =

CALCULATE (

    AVERAGEX (

        ALLSELECTED('Master Time Table'),

        [Testmeasure]

    )

)

This should allow the measure to calculate the correct average regardless of the dimensions present in your matrix visual.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

[v-sgandrathi]

Hi  @Jodallen123,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @Jodallen123,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

[v-sgandrathi]

Hi @Jodallen123,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and give us Kudos. Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[tamerj1 · Accepted solution]

Hi @Jodallen123 

Please try

Test1234 =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Master Time Table',
            'Master Time Table'[YearMonthNo],
            'Master Time Table'[Week of Year]
        ),
        [Testmeasure]
    )
)

[Jodallen123]

Thank you!

This seems to work somewhat as intended. However, it seems I have to explicitly mention all "dimensions" I want the measure to average over, so if I add quarter to the matrix , "above" week and yearmonth, I get an incorrect value. But maybe it's not possible to make the measure completely dynamic? 

[rohit1991]

Hi @Jodallen123 ,
If you want your test1234 measure to calculate the average of [Testmeasure] across distinct weeks while still respecting any filters already applied in the report or visual (such as from slicers or matrix rows). Here's the adjusted DAX code:

test1234 :=
CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            'Master Time Table',
            'Master Time Table'[Week of Year]
        ),[Testmeasure]))