Custom Rows - Sum of Alternate Rows

[Anonymous]

Dear Community,

Need your help to insert custom row that is ratio of sum of alternate rows, like below: =(C2+C4)/(C3+C5)

For ex: Forecast Jan-22 = (3.1+3.3)/(1.2+5.2) = 100% etc

I have about 15 ID for which I need to create YELLOW row calculation:

Many thanks in advance !

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

Unpivot the columns: Jan-22...Dec-22:

Then pivot the columns: measures and value

Add two columns:

Unpivot the columns(except ID and Attribute)

Then pivot the columns attribute and value

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZJBa8MwDIX/Ssm5GEu2Y/u6wc5l1xJG6bxRGCkk3f+f+rQk7qAZY5cviRzr6Una75tAbJtt83QeyvEwXjbP5fg5DKf+ffNQ+vJ2uowvJOfOKKPQGy8MxoLXSGucMCISTStMiGREsklCsiY03XZN8fE8fsuRYTCAWciIMJIzIg4SDhEPIY+IR0lBCl5Xm/3xnMxDcHIW4CxWziKcMTwtFGfxLwYZ2ZV+Vrp9V7Zzhycm1EQg12q7j0Pfl9d780u4n6EhhaJsQh5imCOnD69nAf2gVicQdYIZF9jK2aryMsd6PVpMLSJjMnVRuip56uFSGmlj+TfBm1Gmad2sPpJmYs0U1CurV6devXrFn0m/sm7aj5ne88rVDv6PXfcF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Measures = _t, #"Jan-22" = _t, #"Feb-22" = _t, #"Mar-22" = _t, #"Apr-22" = _t, #"May-22" = _t, #"Jun-22" = _t, #"Jul-22" = _t, #"Aug-22" = _t, #"Sep-22" = _t, #"Oct-22" = _t, #"Nov-22" = _t, #"Dec-22" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Measures", type text}, {"Jan-22", type number}, {"Feb-22", type number}, {"Mar-22", type number}, {"Apr-22", type number}, {"May-22", type number}, {"Jun-22", type number}, {"Jul-22", type number}, {"Aug-22", type number}, {"Sep-22", type number}, {"Oct-22", type number}, {"Nov-22", type number}, {"Dec-22", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Measures"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Measures]), "Measures", "Value"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Forecast/Actual ROI", each ([Forecast Recurring Benefits_1]+[Forecast Recurring Benefits_2])/([Forecast Recurring Costs_1]+[Forecast Recurring Costs_2])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Planned ROI", each ([Planned Recurring Benefits_1]+[Planned Recurring Benefits_2])/([Planned Recurring Costs_1]+[Planned Recurring Costs_2])),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Added Custom1", {"ID", "Attribute"}, "Attribute.1", "Value"),
    #"Pivoted Column1" = Table.Pivot(#"Unpivoted Columns1", List.Distinct(#"Unpivoted Columns1"[Attribute]), "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column1",{"ID", "Attribute.1", "Jan-22", "Feb-22", "Mar-22", "Apr-22", "May-22", "Jul-22", "Jun-22", "Aug-22", "Sep-22", "Oct-22", "Nov-22", "Dec-22"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute.1", "Measures"}})
in
    #"Renamed Columns"

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear @v-jianboli-msft !

Your solution worked as a charm, precisely what I needed. Learnt a lot playing with 'Pivot' & 'Un-Pivot' options, this is one extremely powerful feature.

Thanks !