Best way to calculate cell value in PowerBI

[char23]

Hello, I have a table that I loaded into PowerBI from Excel. Some of the percentage values are imported, and the values reading "null" need to be calculated. What is the best approach to calculate the value for the cell with red text (with using values from the same table to calculate) and return the value back to the table in order to slice and show table in report view. 

I also want to automate this to where everytime there is a new status file, I can calculate and append to an existing table. I included sample data below, but there are multiple values in the same table that I want to calculate, and they use different values in the table to calculate. 

Basically trying to calculate 1000/8500 and report the answer (11.76%) back to the table. 

Thank you for any help in the right direction. 

IMPORTED TABLE

Column 1	Value	Percentage	Status Month
Data 1	8500	20%	24-Jan
Data 2	1000	5%	24-Jan
Metric	2400	null	24-Jan
Metric 1	2000	2%	24-Jan
Data 1	8000	20%	24-Feb
Data 2	500	5%	24-Feb
Metric	2000	null	24-Feb
Metric 1	1500	2%	24-Feb

TABLE IM TRYING TO CALCULATE

Column 1	Value	Percentage	Status Month
Data 1	8500	20%	24-Jan
Data 2	1000	5%	24-Jan
Metric	2400	11.76%	24-Jan
Metric 1	2000	2%	24-Jan
Data 1	8000	20%	24-Feb
Data 2	500	5%	24-Feb
Metric	2000	6.25%	24-Feb
Metric 1	1500	2%	24-Feb

[WanderingBI · Accepted solution]

One possible solution is to use merge:

In order to catch the "Value" of the previous 1 and 2 rows to use for the percentage calculation you can insert index columns. One with 0 offset, one with 1 offset and  one with 2 offset.

Afterwards you can merge the table with the table based on those indexes and then expand the "value" that you need.

Solution code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVQwVNJRsjA1MABSRgaqINJE1ysxTylWB6rACChmaABWYIom75taUpSZrAAWBCvIK83JwarEEGw8xBZsloBdYYDqCrfUJFRXmKI4AiaN5AgDNEegKQFZYmiK4giwilgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, Value = _t, Percentage = _t, #"Status Month" = _t]),
    Types = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Percentage", Percentage.Type}}),

    // add index columns to refer to previous 1 and 2 row(s) to prepare merge
    index0 = Table.AddIndexColumn(Types, "Index0", 0, 1, Int64.Type),
    index1 = Table.AddIndexColumn(index0, "Index1", 1, 1, Int64.Type),
    index2 = Table.AddIndexColumn(index1, "Index2", 2, 1, Int64.Type),

    // Merge on Index0 and Index1 -> get Value
    merge1 = Table.NestedJoin(index2, {"Index0"}, index2, {"Index1"}, "index2.1", JoinKind.LeftOuter),
    merge1expandValue = Table.ExpandTableColumn(merge1, "index2.1", {"Value"}, {"Data1Value"}),
    sort1 = Table.Sort(merge1expandValue,{{"Index0", Order.Ascending}}),
   
    // Merge on Index0 and Index2 -> get Value
    merge2 = Table.NestedJoin(sort1, {"Index0"}, sort1, {"Index2"}, "sort1", JoinKind.LeftOuter),
    merge2expandValue = Table.ExpandTableColumn(merge2, "sort1", {"Value"}, {"Data2Value"}),
    sort2 = Table.Sort(merge2expandValue,{{"Index0", Order.Ascending}}),

    // Calculate Percentage
    percentage = Table.AddColumn(sort2, "Percentage Data2/Data1", each [Data1Value]/[Data2Value], Percentage.Type),
    percentConditional = Table.AddColumn(percentage, "PercentageFinal", each if [Percentage] = null then [#"Percentage Data2/Data1"] else [Percentage], Percentage.Type),

    //Cleanup and reorder
    selectColumns = Table.SelectColumns(percentConditional,{"Column 1", "Value", "Status Month", "PercentageFinal"}),
    reorderColumns = Table.ReorderColumns(selectColumns,{"Column 1", "Value", "PercentageFinal", "Status Month"}),
    renameColumn = Table.RenameColumns(reorderColumns,{{"PercentageFinal", "Percentage"}})
    
in
    renameColumn

[AlienSx · Accepted solution]

let
    Source = your_table,
    mtr = (tbl) => 
        [pct = tbl{[Column 1 = "Data 2"]}[Value] / tbl{[Column 1 = "Data 1"]}[Value],
        upd = tbl{[Column 1 = "Metric "]} & [Percentage = pct],
        replace = Table.ReplaceMatchingRows(tbl, {tbl{[Column 1 = "Metric "]}, upd})][replace],
    group = Table.Group(
        Source, 
        "Column 1",
        {"x", mtr},
        GroupKind.Local, 
        (s, c) => Number.From(c = "Data 1")
    ),
    z = Table.Combine(group[x])
in
    z

[WanderingBI · Accepted solution]

One possible solution is to use merge:

In order to catch the "Value" of the previous 1 and 2 rows to use for the percentage calculation you can insert index columns. One with 0 offset, one with 1 offset and  one with 2 offset.

Afterwards you can merge the table with the table based on those indexes and then expand the "value" that you need.

Solution code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVQwVNJRsjA1MABSRgaqINJE1ysxTylWB6rACChmaABWYIom75taUpSZrAAWBCvIK83JwarEEGw8xBZsloBdYYDqCrfUJFRXmKI4AiaN5AgDNEegKQFZYmiK4giwilgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, Value = _t, Percentage = _t, #"Status Month" = _t]),
    Types = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Percentage", Percentage.Type}}),

    // add index columns to refer to previous 1 and 2 row(s) to prepare merge
    index0 = Table.AddIndexColumn(Types, "Index0", 0, 1, Int64.Type),
    index1 = Table.AddIndexColumn(index0, "Index1", 1, 1, Int64.Type),
    index2 = Table.AddIndexColumn(index1, "Index2", 2, 1, Int64.Type),

    // Merge on Index0 and Index1 -> get Value
    merge1 = Table.NestedJoin(index2, {"Index0"}, index2, {"Index1"}, "index2.1", JoinKind.LeftOuter),
    merge1expandValue = Table.ExpandTableColumn(merge1, "index2.1", {"Value"}, {"Data1Value"}),
    sort1 = Table.Sort(merge1expandValue,{{"Index0", Order.Ascending}}),
   
    // Merge on Index0 and Index2 -> get Value
    merge2 = Table.NestedJoin(sort1, {"Index0"}, sort1, {"Index2"}, "sort1", JoinKind.LeftOuter),
    merge2expandValue = Table.ExpandTableColumn(merge2, "sort1", {"Value"}, {"Data2Value"}),
    sort2 = Table.Sort(merge2expandValue,{{"Index0", Order.Ascending}}),

    // Calculate Percentage
    percentage = Table.AddColumn(sort2, "Percentage Data2/Data1", each [Data1Value]/[Data2Value], Percentage.Type),
    percentConditional = Table.AddColumn(percentage, "PercentageFinal", each if [Percentage] = null then [#"Percentage Data2/Data1"] else [Percentage], Percentage.Type),

    //Cleanup and reorder
    selectColumns = Table.SelectColumns(percentConditional,{"Column 1", "Value", "Status Month", "PercentageFinal"}),
    reorderColumns = Table.ReorderColumns(selectColumns,{"Column 1", "Value", "PercentageFinal", "Status Month"}),
    renameColumn = Table.RenameColumns(reorderColumns,{{"PercentageFinal", "Percentage"}})
    
in
    renameColumn