Forum Discussion

EmmaWyeth's avatar
EmmaWyeth
Frequent Visitor
5 years ago

Measure based on Slicer Selection

Hi All

 

I am trying to write a calculation measure in Power BI, to work out if this week's volumes are greater or less than the average of the last 12 weeks volumes.  If greater then "yes" otherwise "no".

 

I have worked out the rough calculation I need, but am unsure how to look at the week selected in a slicer for the first amount, so it's only looking at the one week?

 

= if([amount]>CALCULATE([amount]/12,'Date'[Last 12 Weeks]="Yes"),"yes","no")
 
Can anyone help?
 
Need Help

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi EmmaWyeth ,

     

    I'm not sure if I've understood your requirement correctly, but to pick up a slicer value to use in a measure you can use SELECTEDVALUE(). For example:

    _example =
IF(
    SELECTEDVALUE(xTable[WeekNumber], 5) = 1,
    SUM(xTable[Sales])
)

     

    Pete

    • EmmaWyeth's avatar
      EmmaWyeth
      Frequent Visitor
      5 years ago

      Hi Pete

       

      I tried SELECTEDVALUES, but it wasn't an option.  Is this a Power BI version thing?

      • BA_Pete's avatar
        BA_Pete
        Super User
        5 years ago

        Hi EmmaWyeth ,

         

        Almost certainly not a Power BI version thing, unless you're on the October 1982 release.

         

        Very difficult to diagnose exactly what the issue is without seeing your actual data/model/calculations etc., but I believe your required measure should look *something* like this, assuming that your slicer selects 'Date'[Week]:

         

         

        Measure = 
VAR selectedAmount =
    CALCULATE(
        [amount],
        'Date'[Week] = SELECTEDVALUE('Date'[Week])
    )
VAR averageAmount =
    CALCULATE(
        [amount],
        'Date'[Last 12 Weeks] = "Yes"
    ) / 12
RETURN
    if(
        selectedAmount > averageAmount,
        "yes",
        "no"
    )

         

         

         

        Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi EmmaWyeth 

    Due to I don't know your data model, so I build a sample to have a test.

     

    Sample Table = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 12, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 ),
    "Volumn", INT ( RAND () * 100 )
)

    Sample Table:

     

    My Volumn is Random for in order to convenience.

    Add two calculated columns YearWeekNum and Rank.

     

    YearWeekNum = 'Sample Table'[Year]*100+'Sample Table'[WeekNum]
    Rank = RANKX('Sample Table','Sample Table'[YearWeekNum],,ASC,Dense)

     

    Build a Slicer Table by YearWeekNum.

     

    Slicer = VALUES('Sample Table'[YearWeekNum])

     

    Measure:

     

    Measure = 
VAR _Select =
    SELECTEDVALUE ( Slicer[YearWeekNum] )
VAR _SelRank =
    CALCULATE (
        MAX ( 'Sample Table'[Rank] ),
        FILTER ( 'Sample Table', 'Sample Table'[YearWeekNum] = _Select )
    )
VAR _SUM12 =
    SUMX (
        FILTER (
            ALL ( 'Sample Table' ),
            'Sample Table'[Rank] < _SelRank
                && 'Sample Table'[Rank] >= _SelRank - 12
        ),
        'Sample Table'[Volumn]
    )
VAR _AVG12 =
    DIVIDE ( _SUM12, 12 )
VAR _SUMSEL =
    SUMX ( FILTER ( ALL ( 'Sample Table' ), 'Sample Table'[Rank] = _SelRank ), 'Sample Table'[Volumn] )
RETURN
    IF ( _SUMSEL > _AVG12, "Yes", "No" )

    Result:

     

    You can download the pbix file from this link: Measure based on Slicer Selection

     

    Best Regards,

    Rico Zhou

     

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