Forum Discussion

hejszyszki's avatar
hejszyszki
Frequent Visitor
5 years ago
Solved

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 li...
Need Help
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    5 years ago

    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's avatar
hejszyszki
Frequent Visitor
5 years ago

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

Fowmy's avatar
Fowmy
Super User
5 years ago

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 )