Add a new column that sum the hours from the operator in the same date

[LuisFelipeTEN95]

Hello,

I'm having some troubles to transform the excel formula in to DAX / Power Quert Power BI that make the SUM of the hours from the column "C" if the operator (column "B") has worked in different stations in the same day (column "A") as:

=SUMIFS (C:C;B:B;B2;A:A;A2)

This formula in to the excel works very well, because the list of operators and date changes:

DATA	OPERATOR	HOUR STD	SUM OPExH.STD
03/01/2023	OPERATOR 1	1,5	5,2
03/01/2023	OPERATOR 1	1,5	5,2
03/01/2023	OPERATOR 1	2,2	5,2
04/01/2023	OPERATOR 2	3,4	7,9
04/01/2023	OPERATOR 2	1,7	7,9
04/01/2023	OPERATOR 2	2,8	7,9

How is it possible to transform this formula in to Power BI?
I tried SUMIF / Calculate / but with no sucess because I need to sum by operator and by date (after that we will calculate the efficiency by the hours STD x ACTUAL)

Thankssssss

[LuisFelipeTEN95]

Hi, @slorin 

Continue:
Today the query is:

let
Fonte = Json.Document(Web.Contents("http://chiv-srv:3304/api/report-cnt4/%20%202022-01-01%2005:59:59")),
#"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela", "Column1", {"F.id", "Exp", "Gruppo", "Cella", "Parte", "Selezione", "Operatore", "Turno", "H.Act", "H.Std", "cy", "ca", "Scarto", "S.Pres.", "Buono", "cntSelezionati", "Sc.%", "minS", "K.Act", "K.Std", "Eff.%"}, {"F.id", "Exp", "Gruppo", "Cella", "Parte", "Selezione", "Operatore", "Turno", "H.Act", "H.Std", "cy", "ca", "Scarto", "S.Pres.", "Buono", "cntSelezionati", "Sc.%", "minS", "K.Act", "K.Std", "Eff.%"}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Column1 Expandido",{{"F.id", Int64.Type}, {"Exp", Int64.Type}, {"Gruppo", type text}, {"Cella", type text}, {"Parte", type text}, {"Selezione", type datetime}, {"Operatore", type text}, {"Turno", type text}, {"H.Act", type number}, {"H.Std", type number}, {"cy", type number}, {"ca", Int64.Type}, {"Scarto", Int64.Type}, {"S.Pres.", Int64.Type}, {"Buono", Int64.Type}, {"cntSelezionati", Int64.Type}, {"Sc.%", type number}, {"minS", Int64.Type}, {"K.Act", type number}, {"K.Std", type number}, {"Eff.%", type number}}),
#"Personalização Adicionada" = Table.AddColumn(#"Tipo Alterado", "DIF. ORE STD x ACT", each [H.Std]-[H.Act]),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Personalização Adicionada",{{"F.id", type text}, {"Selezione", type date}, {"DIF. ORE STD x ACT", type number}}),
#"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado1",{"cntSelezionati", "S.Pres.", "cy", "ca", "Exp"}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Removidas",{{"Parte", "PN"}, {"Selezione", "DATA"}}),
#"Personalização Adicionada1" = Table.AddColumn(#"Colunas Renomeadas", "PN PADRE", each Table.AddColumn([PN], each Text.Combine({Text.Start(Text.From([PN]), 7), "-"}), type text)),
#"Colunas Removidas1" = Table.RemoveColumns(#"Personalização Adicionada1",{"PN PADRE"}),
#"Texto Inserido Após o Delimitador" = Table.AddColumn(#"Colunas Removidas1", "Texto Após o Delimitador", each Text.AfterDelimiter([PN], " "), type text),
#"Colunas Renomeadas1" = Table.RenameColumns(#"Texto Inserido Após o Delimitador",{{"Texto Após o Delimitador", "PN PADRE"}}),
#"Colunas Removidas2" = Table.RemoveColumns(#"Colunas Renomeadas1",{"PN PADRE"})
in
#"Colunas Removidas2"

[slorin]

Hi,

let
Source = Your_Source,
Group = Table.Group(Source, {"DATA", "OPERATOR"}, 
  {{"HOUR STD", each [HOUR STD]},
   {"SUM", each List.Sum([HOUR STD]), type number}}),
Expand = Table.ExpandListColumn(Group, "HOUR STD")
in
Expand

Stéphane

[LuisFelipeTEN95]

Hi @slorin , 
Thanks!
But as my source is a jason file (API REST) do I need to do any other set/configuration?