Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors