Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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.
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
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
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
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |