Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EmmaWyeth
Frequent Visitor

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?
 
7 REPLIES 7
v-rzhou-msft
Community Support
Community Support

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:

1.png

 

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:

2.png

 

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. 

 

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete

 

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

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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?

 

EmmaWyeth_0-1605092052872.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.