Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Custom Rows - Sum of Alternate Rows

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:

ssanka1_0-1681471250298.png

 

Many thanks in advance !

 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous ,

 

Please try:

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

vjianbolimsft_0-1681710268280.png

Then pivot the columns: measures and value

vjianbolimsft_1-1681710329981.png

Add two columns:

vjianbolimsft_2-1681710354021.png

vjianbolimsft_3-1681710372695.png

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:

vjianbolimsft_4-1681710627478.png

 

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.

View solution in original post

2 REPLIES 2
v-jianboli-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous ,

 

Please try:

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

vjianbolimsft_0-1681710268280.png

Then pivot the columns: measures and value

vjianbolimsft_1-1681710329981.png

Add two columns:

vjianbolimsft_2-1681710354021.png

vjianbolimsft_3-1681710372695.png

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:

vjianbolimsft_4-1681710627478.png

 

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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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 !

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)