cancel
Showing results for
Did you mean:
Frequent Visitor

## Changing average using slicer

Hi! Below i have a graph, made of measure below which shows 4-w Average. (Material code has relationship with area in another table to be clear).  Structure of table is also shown below. Weeks are like from 1 to 78 (W1 - W78). What i would like to accomplish is to make slicer(with slider/zip) that shows range from W1 to last Week and once you pick certain range like W2-W8 it shows average of W2-W8.
Does anyone have any idea how to do it? Is it even possible to do :)?

1 ACCEPTED SOLUTION
Super User IV

@hejszyszki

You need to create another column in this table to extract the week number.

``WeekNumber = INT(SUBSTITUTE(Table12[WeekNum],"W",""))``

Then, you can use below measure and make sure you have slicer on the WeekNumber to select the range:

``````measure =
VAR __minweekno =
MIN ( 'invo dos'[WeekNumber] )
VAR __maxnweekno =
MAX ( 'invo dos'[WeekNumber] )
RETURN
SUMX (
FILTER (
'invo dos',
'invo dos'[SNP Plan] = "Short Supply"
&& 'invo dos'[WeekNumber] >= __minweekno
&& 'invo dos'[WeekNumber] <= __maxnweeknow
),
'invo dos'[Value]
)
``````

Note: If your data expands into more than one year then you will have to add (Year + weekNumber )

Proud to be a Super User!

3 REPLIES 3
Super User IV

@hejszyszki
The best approach to your problem is to unpivot your Weeks into two columns as follows

Material Code, Desc, SNP Plan, Week, Value

Refer to these videos on UnPivot: https://www.youtube.com/watch?v=Vff2kRBM95o&t=0s

Proud to be a Super User!

Frequent Visitor

Ended up with something like this. Stuck when trying to create slicer of average of picked range.

Super User IV

@hejszyszki

You need to create another column in this table to extract the week number.

``WeekNumber = INT(SUBSTITUTE(Table12[WeekNum],"W",""))``

Then, you can use below measure and make sure you have slicer on the WeekNumber to select the range:

``````measure =
VAR __minweekno =
MIN ( 'invo dos'[WeekNumber] )
VAR __maxnweekno =
MAX ( 'invo dos'[WeekNumber] )
RETURN
SUMX (
FILTER (
'invo dos',
'invo dos'[SNP Plan] = "Short Supply"
&& 'invo dos'[WeekNumber] >= __minweekno
&& 'invo dos'[WeekNumber] <= __maxnweeknow
),
'invo dos'[Value]
)
``````

Note: If your data expands into more than one year then you will have to add (Year + weekNumber )

Proud to be a Super User!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.