Visualise percentage of column by values, on a rolling 4 period basis

[Chris_Kennedy]

Hello!

My data set includes individual rows for a number of store audit visits that were either "completed" or "not completed" on a weekly basis. I currently have data for 10 weeks, but this will grow over a period of years.

I need to visualise the percentage of audits that were "completed" over time, but this must be on a 4 week rolling basis. For example, filtering on week 7 should show % of all audits that were completed in weeks 4, 5, 6 and 7.

I was able to do this on a static basis for the latest 4 weeks, by using three measures (one to count the total number of rows, one to count the number of rows that say "completed", and one to divide completed / total) and then applying a "TopN" filter to the gauge visual I created to only include the latest 4 weeks (which are in yymm format).

However, I need to be able to do this dynamically, so that a slicer can be used to select a week and see the 4 week rolling % completed for latest 4 weeks (example 1). I also need to display the 4 week rolling averages in a line or bar chart over time, where each data point includes the latest 4 weeks rather than only the week on the axis (example 2).

Any help would be really appreciated!

example 1:

example 2:

Thanks

[v-yingjl · Accepted solution]

Hi @Chris_Kennedy ,

You can extract the week column as a new table, use it in the slicer:

Week table = DISTINCT('Table'[Week])

Create a measure like this:

Rolling average = 
AVERAGEX (
    FILTER (
        'Table',
        'Table'[Week]
            >= SELECTEDVALUE ( 'Week table'[Week] ) - 3
            && 'Table'[Week] <= SELECTEDVALUE ( 'Week table'[Week] )
    ),
    COUNTX ( FILTER ( 'Table', 'Table'[Status] = "Completed" ), [Week] )
)
    / AVERAGEX (
        FILTER (
            'Table',
            'Table'[Week]
                >= SELECTEDVALUE ( 'Week table'[Week] ) - 3
                && 'Table'[Week] <= SELECTEDVALUE ( 'Week table'[Week] )
        ),
        COUNTROWS ( 'Table' )
    )

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chris_Kennedy]

Thanks very much for your help. I followed the steps by creating a table called "Week table" and then a measure by adapting the example you kindly provided, however the Rolling Average measure I created does not display any data when added to a visual (see example below).

Can you see any obvious errors in the measure I created? Please note I have tried the section in bold below both with and without the "Bluebird Data" table prefix, but neither seems to work.

Rolling average =

AVERAGEX (

FILTER (

'Bluebird Data',

'Bluebird Data'[ESA Week]

>= SELECTEDVALUE ( 'Week table'[ESA Week] ) - 3

&& 'Bluebird Data'[ESA Week] <= SELECTEDVALUE ( 'Week table'[ESA Week] )

),

COUNTX ( FILTER ( 'Bluebird Data', 'Bluebird Data'[ESA Status] = "Completed" ), [ESA Week] )

)

/ AVERAGEX (

FILTER (

'Bluebird Data',

'Bluebird Data'[ESA Week]

>= SELECTEDVALUE ( 'Week table'[ESA Week] ) - 3

&& 'Bluebird Data'[ESA Week] <= SELECTEDVALUE ( 'Week table'[ESA Week] )

),

COUNTROWS ( 'Bluebird Data' )

)

Thanks

Chris

[v-yingjl]

Hi @Chris_Kennedy ,

Have you select the field in the slicer? Since the formula used selectedvalue() fucntion, if you do not select one field in the slicer, the visual chart would show blank.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chris_Kennedy]

Thanks for your help, that has done it!