Trying to show evolving average based on number of records

[dedelman_clng]

I have a data set that looks like the following

Index	Business Unit	Days to Hire
1	Operations	13
2	Operations	51
3	Head Office	41
4	Operations	98
5	Head Office	205
6	Operations	172
7	Operations	237
8	Operations	112
9	Operations	126
10	Operations	27
11	Operations	153
12	Head Office	42
13	Head Office	72

"Index" was added in PowerQuery, representing an ascending date field (e.g. 1 = Jan 1, 2 = Jan 5, 3 = Jan 17, etc). User wants to see the average of the "Days to Hire" column on a line chart where the X-axis is the number of records used to calculate the average. It was done in Excel like this:

The tricky part is user wants the line chart to be sliceable by Business Unit, so I can't use the just the Index field as the axis (numbers will be skipped). I know I can write a measure to dynamically count the number of records with an index <= the "current" index, but a measure can't be used as an X-axis. Calculated column doesn't refresh upon using the slicer, so that doesn't work either.

Anyone have the same use case and found a way to solve it?

Thanks

David

[v-yuta-msft · Accepted solution]

@dedelman_clng ,

I'm afraid this can't achieved by using slicer and changing settings in the X-axis of line chart. As a workaround, you may try create several calculate columns using different filter condition instead of slicer, then create several line charts separately using these calculate columns in different pages. Finally create several bookmarks and buttons on these pages and configure the button action.

For more details about bookmark, please refer to doc below:

https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dedelman_clng]

Thanks @v-yuta-msft .  I don't think that bookmarks is a good strategy in my specific case, as the user will be accessing the dashboard thru Power BI Desktop. But its good to know I'm not missing something obvious.

Thanks

David