Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |