Forum Discussion
Changing average using slicer
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 )
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
Ended up with something like this. Stuck when trying to create slicer of average of picked range.
Thanks in advance!
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 )