Getting a sum of a specific colum

[SvenJ]

I have to add a column dynamically. How can i get a sum in advanced editor -> f. ex

I need the sum added to colum 1051 of the column "Umsatz Values" where "BWA Zeile" has the value "1051"

Can someone help me?

[lbendlin]

Use Grouping aggregations or List.Accumulate

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

[SvenJ]

Thanks @lbendlin for your support.

As you can see on the screenshot below the row 1060 and 1080 is on some month different. I have no idea, why.

1060 is column which is specific in the data table called "BWA Zeile" - but 1080 need to be dynamic and is not just the sum. Sometimes it is f. ex 1051 minus 1080 (but the value of 1080  is not correct). 

Here is my advance code.

let
    Quelle = Folder.Files(Datenpfad & Beraternummer & "-" & Mandantennummer & "\Buchungsdaten"),
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(Quelle, each [Attributes]?[Hidden]? <> true),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", Table.ColumnNames(#"Datei transformieren"(Buchungsdaten_Model))),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte Tabellenspalte1",{{"Source.Name", type text}, {"Konto", Int64.Type}, {"Belegdatum", Int64.Type}, {"BU-Schlüssel", type any}, {"Gegenkonto", Int64.Type}, {"Buchungstext", type text}, {"Steuersatz", Int64.Type}, {"Belegfeld 1", type text}, {"Umsatz Soll", type number}, {"Umsatz Haben", type number}, {"BSNr", Int64.Type}, {"Leistungsdatum", type any}, {"Generalumkehr (GU)", Int64.Type}, {"Stapel-Nr.", type text}}),
    #"Eingefügter Textbereich" = Table.AddColumn(#"Geänderter Typ", "Textbereich", each Text.Middle([#"Stapel-Nr."], 0, 2), type text),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Eingefügter Textbereich",{{"Textbereich", Int64.Type}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ1",{{"Textbereich", "Monat"}}),
    #"Eingefügter Textbereich1" = Table.AddColumn(#"Umbenannte Spalten", "Textbereich", each Text.Middle([#"Stapel-Nr."], 3, 4), type text),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Eingefügter Textbereich1",{{"Textbereich", Int64.Type}}),
    #"Umbenannte Spalten2" = Table.RenameColumns(#"Geänderter Typ2",{{"Textbereich", "Jahr"}}),
    #"Zusammengeführte Abfragen" = Table.NestedJoin(#"Umbenannte Spalten2", {"Konto"}, #"BWA Kontenzuordnung", {"Konto"}, "Konten", JoinKind.LeftOuter),
    #"Erweiterte Konten" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Konten", {"BWA Zeile", "S/H", "Zeilenbeschriftung"}, {"Konten.BWA Zeile", "Konten.S/H", "Konten.Zeilenbeschriftung"}),
    #"Umbenannte Spalten3" = Table.RenameColumns(#"Erweiterte Konten",{{"Konten.BWA Zeile", "BWA Zeile"}, {"Konten.Zeilenbeschriftung", "Zeilenbeschriftung"}, {"Konten.S/H", "S/H"}}),
    #"Umsatz Values" = Table.AddColumn(#"Umbenannte Spalten3", "Umsatz Values", each if [#"S/H"] = "S" then [Umsatz Soll] - [Umsatz Haben] else [Umsatz Haben] - [Umsatz Soll]),
    #"Geänderter Typ7" = Table.TransformColumnTypes(#"Umsatz Values",{{"Umsatz Values", type number}, {"BWA Zeile", Int64.Type}}),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Geänderter Typ7", each ([BWA Zeile] <> null)),
    #"S1051" = Table.AddColumn(#"Gefilterte Zeilen1", "1051", each if (([BWA Zeile] >= 1020) and ([BWA Zeile] <= 1045)) then[Umsatz Values] else 0),
    S1080 = Table.AddColumn(S1051, "1080",  each (if [BWA Zeile] = 1060 then [Umsatz Values]  else 0)),
    #"S1092" = Table.AddColumn(S1080, "1092", each [1080] +  (if ([BWA Zeile] >= 1080) and ([BWA Zeile] <= 1090)  then [Umsatz Values] else 0)),
    #"S1280" = Table.AddColumn(#"S1092", "1280", each if (([BWA Zeile] >= 1100) and ([BWA Zeile] <= 1260)) then [Umsatz Values] else 0),
    #"S1300" = Table.AddColumn(#"S1280", "1300", each [1092] - [1280]),
    #"S1320" = Table.AddColumn(#"S1300", "1320", each  if (([BWA Zeile] >= 1310) and ([BWA Zeile] <= 1312)) then [Umsatz Values] else 0),
    #"S1330" = Table.AddColumn(#"S1320", "1330", each if (([BWA Zeile] >= 1320) and ([BWA Zeile] <= 1324)) then[Umsatz Values] else 0),
    #"S1345" = Table.AddColumn(#"S1330", "1345", each [1300] - [1320] + [1330]),
    #"S1380" = Table.AddColumn(#"S1345", "1380", each [1345] - (if ([BWA Zeile] = 1355) then[Umsatz Values] else 0)),
    #"Geänderter Typ6" = Table.TransformColumnTypes(#"S1380",{{"Umsatz Values", type number},{"1080", type number}, {"1092", type number}, {"1280", type number}, {"1300", type number},{"1320", type number}, {"1330", type number}, {"1345", type number},{"1380", type number},{"1051", type number}}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Geänderter Typ6",{"Source.Name", "Konto", "Belegdatum", "BU-Schlüssel", "Gegenkonto", "Buchungstext", "Steuersatz", "Belegfeld 1", "Umsatz Soll", "Umsatz Haben", "BSNr", "Leistungsdatum", "Generalumkehr (GU)", "Stapel-Nr.", "Monat", "Jahr", "Zeilenbeschriftung", "S/H", "Umsatz Values", "BWA Zeile"}),
    #"PivotSpalte" = Table.Pivot(Table.TransformColumnTypes(#"Neu angeordnete Spalten", {{"BWA Zeile", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Neu angeordnete Spalten", {{"BWA Zeile", type text}}, "de-DE")[#"BWA Zeile"]), "BWA Zeile", "Umsatz Values", List.Sum),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(PivotSpalte, {"S/H", "Zeilenbeschriftung", "Jahr", "Monat", "Stapel-Nr.", "Generalumkehr (GU)", "Leistungsdatum", "BSNr", "Umsatz Haben", "Umsatz Soll", "Belegfeld 1", "Steuersatz", "Buchungstext", "Gegenkonto", "BU-Schlüssel", "Belegdatum", "Konto", "Source.Name"}, "Attribut", "Wert"),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Entpivotierte andere Spalten", each not Text.EndsWith([#"Stapel-Nr."], "-2")),
    #"Umbenannte Spalten4" = Table.RenameColumns(#"Gefilterte Zeilen",{{"Attribut", "BWA Zeile"}}),
    #"Geänderter Typ4" = Table.TransformColumnTypes(#"Umbenannte Spalten4",{{"Belegdatum", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ4", "Belegdatum_2", each 
    if Text.Length([Belegdatum]) = 7 then Text.Start([Belegdatum],1) & "." & Text.Range([Belegdatum],1,2) & "." & Text.End([Belegdatum],4)
    else
    Text.Start([Belegdatum],2) & "." & Text.Range([Belegdatum],2,2) & "." & Text.End([Belegdatum],4)),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte",{{"Belegdatum_2", type date}}),
    #"Der Text vor dem Trennzeichen wurde eingefügt." = Table.AddColumn(#"Geänderter Typ3", "Text vor Trennzeichen", each Text.BeforeDelimiter([#"Stapel-Nr."], "/"), type text),
    #"Umbenannte Spalten5" = Table.RenameColumns(#"Der Text vor dem Trennzeichen wurde eingefügt.",{{"Text vor Trennzeichen", "Stapeldatum"}}),
    #"Geänderter Typ5" = Table.TransformColumnTypes(#"Umbenannte Spalten5",{{"Stapeldatum", type date}})
in
     #"Geänderter Typ5"

Can you  help me? I can give away the date source, i´m sorry. But maybe you have an idea, what to do.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[SvenJ]

Here is the sample file - the expected result is 

row 1080 = row 1051 - row 1060 = -68500 

in this example

row 1080 = 0 - 68500 = 68500 (correct)

but the example shows 

row 1080 = row 1051 - row 1060 = -57500 (which is mathematically wrong)

in my file:

row 1080 = 0 - 68500 = -57500 (wrong)

Download sample file:

New Sample File

[lbendlin]

Sorry, cannot access the sample file. It asks for a login.

[SvenJ]

Sorry,

here is the link to the Sample File on Google drive

[lbendlin]

Thank you for the sample file.  I see that the Kalender table is not joined into the data model. Is that intentional?

[SvenJ]

No, it isn´t. It would be better, if it´s connected. I thought it is 😉

[lbendlin]

Do you want to connect it to the Belegdatum, LeistungsDatum or Stapeldatum? From the measures it seems like you are trying to do time intelligence operations with the Stapeldatum.

[SvenJ]

Dear @lbendlin 
Thank you for your commitment and your help. I'm supposed to present the numbers on Tuesday and need a solution. I don't want to "stress" you, but maybe you can help me.

[lbendlin]

Please understand that we are all volunteer users here. There is no SLA, nor is there a guarantee that a solution can be proposed. Have you considered involving a professional Power BI consulting company?

[SvenJ]

That's what i said / i dont want to stress anyone. It was just a question. No, that's not an option for the moment

[lbendlin]

See if the attached is getting you closer to what you need.

[SvenJ]

Dear @lbendlin ,

thanks for your help - i really appreciate it.

ROW 1080 is correct in this case. But the problem ist that the rows need to be calculated differently.

f.ex.

1080 = 1051 - 1060

1092 = 1080 + sum of bwa zeile >= 1080 and bwa zeiule <= 1090

Here are the different calculation formulas for the rows:

  #"S1051" = Table.AddColumn(#"Gefilterte Zeilen1", "1051", each if (([BWA Zeile] >= 1020) and ([BWA Zeile] <= 1045)) then[Umsatz Values] else 0),
    S1080 = Table.AddColumn(S1051, "1080",  each [1051] - (if [BWA Zeile] = 1060 then [Umsatz Values]  else 0)),
    #"S1092" = Table.AddColumn(S1080, "1092", each [1080] +  (if ([BWA Zeile] >= 1080) and ([BWA Zeile] <= 1090)  then [Umsatz Values] else 0)),
    #"S1280" = Table.AddColumn(#"S1092", "1280", each if (([BWA Zeile] >= 1100) and ([BWA Zeile] <= 1260)) then [Umsatz Values] else 0),
    #"S1300" = Table.AddColumn(#"S1280", "1300", each [1092] - [1280]),
    #"S1320" = Table.AddColumn(#"S1300", "1320", each  if (([BWA Zeile] >= 1310) and ([BWA Zeile] <= 1312)) then [Umsatz Values] else 0),
    #"S1330" = Table.AddColumn(#"S1320", "1330", each if (([BWA Zeile] >= 1320) and ([BWA Zeile] <= 1324)) then[Umsatz Values] else 0),
    #"S1345" = Table.AddColumn(#"S1330", "1345", each [1300] - [1320] + [1330]),
    #"S1380" = Table.AddColumn(#"S1345", "1380", each [1345] - (if ([BWA Zeile] = 1355) then[Umsatz Values] else 0)),

Do you have any idea?

[lbendlin]

My previous post was showing an implementation as a measure. You seem to prefer an implementation as a calculated column or directly in Power Query? Do you want to create additional columns for each rule, or do you want to replace existing values in the same column?

[SvenJ]

Do you want to create additional columns for each rule

Yes, as it is not 100% clear, how the structure is at the end. I would prefer the easiest way. What's the most "safe" way?

[lbendlin]

From a data preparation perspective the safest way is not to destroy any information - this means creating new columns for each of the rules.

From a data visualization perspective that is the worst possible outcome.

[SvenJ]

What's your suggestion? ... and maybe solution?

[lbendlin]

I would keep the source data as is and would implement the logic in DAX, similar to what I have shown for the 1080 case.

[SvenJ]

Can you give me an example how to add a second rule? Maybe i can get it down by myself if i have an example.