Create a measure that can be filtered and returns monthly average for weekly data

[tommy24]

Hi

Basically i have a bar and line chart and what i want for the bars is to have the mean of a lead time on a weekly basis. Then i created a measure so that the monthly average would be calculated and placed over the corresponding weeks. The problem is that when i use a slicer to filter the data, for example for a particular client, it doesn't recalculate the values for the measure. 

As shown in the images the pattern and values for the line remain the same. 

Here is the measure that i created: 

MeanLT =

VAR MonthYear = CONCATENATE(Data[Year], Data[Month])

RETURN

    AVERAGEX(

        FILTER(

            Data,

            CONCATENATE(Data[Year], Data[Month]) = MonthYear &&

        ),

        Data[LT]

    )

Here is the file with an Example

Can it be done?

Thanks 

 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[tommy24]

Just updated the original post @lbendlin 

The objective is to be able to select any of the clients in the slicer and get the monthly average calculated correctly. 

In the future i'd like to add other slicers and still manage to get the average recalculated.

Thanks again

[lbendlin · Accepted solution]

Replace

Average = 
VAR MonthYear = CONCATENATE(VALUES(Data[Year]), VALUES(Data[Month]))
RETURN
    AVERAGEX(
        FILTER(
            ALL(Sheet1),
            CONCATENATE(Sheet1[Year], Sheet1[Month]) = MonthYear 
        ),
        Sheet1[LT]
    )

with 

Average = 
VAR MonthYear = CONCATENATE(VALUES(Data[Year]), VALUES(Data[Month]))
RETURN
    AVERAGEX(
        FILTER(
            ALLSELECTED(Sheet1),
            CONCATENATE(Sheet1[Year], Sheet1[Month]) = MonthYear 
        ),
        Sheet1[LT]
    )