cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NanDeb
Helper I
Helper I

Filter context and Matrix Visual - Weekly average for selected period

Hi, I am  fairly new to power BI  and have a problem I cannot solve, Ihave searched the boards and can't find a solution. 

 

I would like to display the weekly sales average for a selected period in a matrix visual with the weekending days for the selected period as the columns. 

 

I have a calculated table that return a dynamic date period (essentially last 4, 8, 12 weeks) and filters the Date table, which in turn is directly related to the Sales table.

 

So I can offer the user the option to view only the last 4, 8, 12 weeks of Sales. 

 

I have a requirement to return a matrix with product code as rows and weekending dates as columns that show total sales, and averages sales for the period selected. 

 

I need a measure that retains the time period filters for the average but removes the calendar filters on the visual so that each week column has the same average sales. 

 

Current attempt (returns the average for the week column - which is the same as the sales value obviously) is:

 

Currently my Weekly Sales for the Selected Period is Calculated like this:

 

 

Sales Period Selected =
VAR MinWeek =
    CALCULATE (
        MINX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] = MIN ( 'Time Period Selector'[Date] )
            ),
            'Calendar'[WeekEnding]
        ),
        ALLSELECTED ( 'Time Period Selector' )
    )
VAR MaxWeek =
    CALCULATE (
        MAXX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] = MAX ( 'Time Period Selector'[Date] )
            ),
            'Calendar'[WeekEnding]
        ),
        ALLSELECTED ( 'Time Period Selector' )
    )
VAR FirstForecastWeek =
    CALCULATE (
        MAXX (
            FILTER (
                ALLSELECTED ( 'Calendar' ),
                'Calendar'[IsFutureDay] = FALSE ()
                    && 'Calendar'[IsCompletedWeek] = TRUE ()
            ),
            ( 'Calendar'[WeekEnding] )
        )
    )
RETURN
    CALCULATE (
        [Sales Switch],
        FILTER (
            Sales,
            Sales[Tax date] > MinWeek
                && Sales[Tax date] <= FirstForecastWeek
        )
    )

 

 And the weekly average is like this: 

 

Averagy Weekly Sales =
AVERAGEX(
    SUMMARIZE(
        ALLSELECTED('Calendar'), 
        'Calendar'[WeekEnding]
        ), 
    [Sales Period Selected]
)

 

 

Which becasue of the Filter Context in the table gives the same value for the week as the sales (average of weekly sales over 1 week = sum of sales) 
 If I remove filters like this 

 

AVERAGEX(
    SUMMARIZE(
        ALLSELECTED('Calendar'), 
        'Calendar'[WeekEnding]
        ), 
      [Sales Period Selected]
       ), 
   REMOVEFILTERS('Calendar')
)

 

 

I get the right value  (So the correct average over the number of weeks in the selected period) BUT... the martix visual then retunrs ALL the weeks in the calendar table 

NanDeb_0-1670062981053.png

Red weeks NOT in period, Yellow in period

Please help

 

1 ACCEPTED SOLUTION
NanDeb
Helper I
Helper I

Solved:

I had to remove the filters from the calendar so that the calculation could be done over the full set of weeks but retain them for time period selected.  

CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            ALLSELECTED('Calendar'),
            'Calendar'[WeekEnding]
            ),
        [Sales Period Selected]
),  REMOVEFILTERS('Calendar'),
   KEEPFILTERS('Time Period Selector'[Time Period] = SELECTEDVALUE('Time Period Selector'[Time Period])
)
)




View solution in original post

2 REPLIES 2
NanDeb
Helper I
Helper I

Solved:

I had to remove the filters from the calendar so that the calculation could be done over the full set of weeks but retain them for time period selected.  

CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            ALLSELECTED('Calendar'),
            'Calendar'[WeekEnding]
            ),
        [Sales Period Selected]
),  REMOVEFILTERS('Calendar'),
   KEEPFILTERS('Time Period Selector'[Time Period] = SELECTEDVALUE('Time Period Selector'[Time Period])
)
)




NanDeb
Helper I
Helper I

Can anyone help? Do you need more information? 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors