Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm looking for some help creating a calculated column, whereby the average of the last 3 values recorded for a city are shown.
I'm getting lost somewhere after AVERAGEX(TOPN,3 - if that is even the right way to start to solve...
City | Date | Temp | Average Last 3 Temps for City |
London | 21/7/2021 | 21 | 19.6 |
New York | 21/7/2021 | 18 | 18.3 |
London | 24/7/2021 | 24 | 20.1 |
Paris | 25/7/2021 | 22 | 21.1 |
London | 27/7/2021 | 21 | 22 |
Solved! Go to Solution.
@tomshaw83 you can use this measure
Measure =
VAR _upper =
MAX ( 'Table'[Date] )
VAR _city =
MAX ( 'Table'[City] )
VAR _lower =
MINX (
TOPN (
3,
FILTER ( ALL ( 'Table' ), 'Table'[City] = _city && 'Table'[Date] <= _upper ),
'Table'[Date], DESC
),
'Table'[Date]
)
VAR _avg =
CALCULATE (
AVERAGE ( 'Table'[Temp] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] >= _lower
&& 'Table'[Date] <= _upper
),
ALLEXCEPT ( 'Table', 'Table'[City] )
)
RETURN
_avg
Calculated column
Column =
VAR _upper =
CALCULATE(MAX ( 'Table'[Date] ))
VAR _city =
CALCULATE(MAX ( 'Table'[City] ))
VAR _lower =
MINX(
TOPN (
3,
FILTER ( ALL ( 'Table' ), 'Table'[City] = _city && 'Table'[Date] <= _upper ),
'Table'[Date], DESC
),'Table'[Date])
VAR _avg =
CALCULATE (
AVERAGE ( 'Table'[Temp] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] >= _lower
&& 'Table'[Date] <= _upper
)
,ALLEXCEPT('Table','Table'[City]))
RETURN
_avg
Pbix is attached
@tomshaw83 you can use this measure
Measure =
VAR _upper =
MAX ( 'Table'[Date] )
VAR _city =
MAX ( 'Table'[City] )
VAR _lower =
MINX (
TOPN (
3,
FILTER ( ALL ( 'Table' ), 'Table'[City] = _city && 'Table'[Date] <= _upper ),
'Table'[Date], DESC
),
'Table'[Date]
)
VAR _avg =
CALCULATE (
AVERAGE ( 'Table'[Temp] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] >= _lower
&& 'Table'[Date] <= _upper
),
ALLEXCEPT ( 'Table', 'Table'[City] )
)
RETURN
_avg
Calculated column
Column =
VAR _upper =
CALCULATE(MAX ( 'Table'[Date] ))
VAR _city =
CALCULATE(MAX ( 'Table'[City] ))
VAR _lower =
MINX(
TOPN (
3,
FILTER ( ALL ( 'Table' ), 'Table'[City] = _city && 'Table'[Date] <= _upper ),
'Table'[Date], DESC
),'Table'[Date])
VAR _avg =
CALCULATE (
AVERAGE ( 'Table'[Temp] ),
FILTER (
ALL ( 'Table'[Date] ),
'Table'[Date] >= _lower
&& 'Table'[Date] <= _upper
)
,ALLEXCEPT('Table','Table'[City]))
RETURN
_avg
Pbix is attached
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |