Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

grouped rows in power query to show the amount in one column once, not a sumation

I have a huge table with multiple PO's, in my case the PO amount is a gross sum, and then there are different lines that are there to show how much of that gross amonut was spent.  I need to see all the lines that show the amount spent, but not have the gross amount be multiolied by how many rows there are. I cant find a way to only show the un multiplied gross amount when I am grouping the rows.

Below is an example of what I am trying to see as a result of this.  The PO Amount is the total gross amount allocated to the PO, and the amount spent shows the individual purchases that have used some of the gross amount and is shown in the total line.

 

                        PO #      Gross amount    spend

                     123456        $4500               $1300

                     123456        $4500               $1200

totals            123456        $4500               $2500

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Syndicated - Outbound

Hi @Anonymous 

Just group on the first two columns.

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRUjExNTAA0YbGQDpWB6uUEUgqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t, #"Gross amount" = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}, {"Gross amount", Currency.Type}, {"Spend", Currency.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #", "Gross amount"}, {{"Total spend", each List.Sum([Spend]), type nullable number}})
in
    #"Grouped Rows"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

1 REPLY 1
AlB
Community Champion
Community Champion

Syndicated - Outbound

Hi @Anonymous 

Just group on the first two columns.

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRUjExNTAA0YbGQDpWB6uUEUgqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t, #"Gross amount" = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}, {"Gross amount", Currency.Type}, {"Spend", Currency.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #", "Gross amount"}, {{"Total spend", each List.Sum([Spend]), type nullable number}})
in
    #"Grouped Rows"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

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