Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Lucifer2019
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"
|
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. |
Hi @Lucifer2019
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"
|
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.