Excel BI Challenge Workout 002

Workouts Excel BI Challenge
21

LinkedIn Post by:
Taeyong Shin

365
=SUM( TAKE(FILTER(A2:A10, A2:A10), -3) )

Legacy
=SUM( INDEX(A2:A10, AGGREGATE(14, 6, (ROW(A2:A10)-ROW(A1))/SIGN(A2:A10), ROW(1:3)) ) )

22

LinkedIn Post by
N’rele Ferdinand Attobra

=SUM(INDEX(FILTER(A2:A10,A2:A10<>0),SEQUENCE(3,,COUNT(FILTER(A2:A10,A2:A10<>0)),-1)))

23

LinkedIn Post by:
Navid Esmaeilzadeh

=SUM(INDEX(A:A,LARGE(IF(A2:A10>0,ROW(A2:A10),""),ROW(A1:A3))))

24

LinkedIn Post by:
Jardiel Euflázio

=SUM(INDEX(A2:A10,LARGE((A2:A10<>0)*SEQUENCE(ROWS(A2:A10)),{1\2\3})))

25

LinkedIn Post by:
Hakeem Lawrence, MSBA

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
 NonZeroRows = Table.SelectRows(#"Changed Type", each ([Data] <> 0)),
 Last3Total = List.Sum(Table.LastN(NonZeroRows, 3)[Data])
in
 Last3Total
26

LinkedIn Post by
Aditya Kumar Darak

Power Query Can be like this one.

let
 Source  = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content][Data],
 Filtered = List.RemoveItems ( Source, { 0 } ),
 LastItems = List.LastN ( Filtered, 3 ),
 Sum    = List.Sum ( LastItems )
in
 Sum
27

LinkedIn Post by
Aditya Kumar Darak

Another Power Query may be:

let
 Source  = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
 Filtered = Table.SelectRows ( Source, each ( [Data] <> 0 ) ),
 LastItems = Table.LastN ( Filtered, 3 ),
 Sum    = List.Sum ( LastItems[Data] )
in
 Sum
28

LinkedIn Post by:
Víctor Manuel Santos Maldonado

=LET(Y,FILTRAR($B$2:$B$10,$B$2:$B$10>0),X,FILAS(Y),SUMA(INDICE(Y,X),INDICE(Y,X-1),INDICE(Y,X-2)))

29

LinkedIn Post by:
محمد حلمي

=SUM(INDEX(A2:A10;LARGE(IF(A2:A10<>0;ROW(A2:A10)-1);ROW(1:3))))

30

LinkedIn Post by:
Juliano Santos Lima

Old fashioned way.
=SUM(INDEX(A2:A10,LARGE(IF(A2:A10<>0,ROW(A2:A10)-1),{1;2;3}),))

31

LinkedIn Post by:
Jardiel EuflázioView Jardiel Euflázio’s profile

=SUM(TAKE(FILTER(A2:A10,A2:A10<>0),-3))