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" otherwi...
Need Help
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

EmmaWyeth's avatar
EmmaWyeth
Frequent Visitor
5 years ago

It is telling me that SELECTEDVALUE is not a valid function name.  Any ideas?

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Ok. I guess you are using Direct Query mode rather than import?

     

    Try this instead. I'm working on the assumption that your slicer is set to single-select on 'Date'[Week]:

     

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

     

     

    Pete

    • EmmaWyeth's avatar
      EmmaWyeth
      Frequent Visitor
      5 years ago

      Sorry for the delay, got called away on other reports.

       

      Just tried this new bit of code, but am getting a syntax error.  It reads exactly the same as yours above, any ideas?