Stacked Bar Chart Help - sum of payments by category across dates

[Willbw02]

I'm new at this and I'm banging my head against my screen trying to figure this out.  😫

I have two categories of payments, 1 & 2.  Each payment has a projected payment date.  I'd like to get a stacked bar chart showing the SUM of the payment amounts by month across those two categories.  I feel like I'm running into errors creating this with wither linking to my Calendar Table and/or figuring out how to unpivot my data.  Example table is below.

Order Number	Order Date	Customer	Sales Rep	Total Price	Payment 1	Payment 1 Date	Payment 2	Payment 2 Date
1001	5/1/2023	Delta	Mike	25600.00	25600.00	12/3/2023
1002	5/23/2023	Bravo	Molly	21578.00	10789.00	11/5/2023	10789.00	6/9/2024
1003	6/1/2023	Alpha	Mike	36296.00	18148.00	11/9/2023	18148.00	6/25/2024
1004	6/14/2023	Alpha	Peter	95024.00	95024.00	5/1/2025
1005	6/8/2023	Bravo	John	13745.00	6872.50	8/19/2024	6872.50	8/30/2025
1006	6/8/2023	Alpha	Mike	18364.00	9182.00	11/7/2023	9182.00	5/1/2024
1007	7/9/2023	Charlie	John	48364.00	48364.00	3/1/2024
1008	7/25/2023	Charlie	John	25600.00	12800.00	6/1/2024	12800.00	10/1/2024
1009	7/29/2023	Charlie	Peter	16573.00	16573.00	1/5/2024
1010	8/4/2023	Delta	Mike	13587.00	13587.00	4/7/2024
1011	8/5/2023	Bravo	Molly	13248.00	6624.00	9/25/2023	6624.00	4/1/2024

Any help is much appreciated.

Thanks

Willbw02

[Ashish_Mathur]

Hi,

This M code will transform your data into one that will be amenable to easy analysis/slicer creation.

Hope this helps.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Total Price"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Order Number", "Order Date", "Customer", "Sales Rep"}, "Attribute", "Value"),
    #"Extract payment instance" = Table.AddColumn(#"Unpivoted Other Columns", "Payment instance", each Text.Middle([Attribute], 8, 1), type text),
    #"Added Custom" = Table.AddColumn(#"Extract payment instance", "Nature", each if Text.Contains([Attribute],"Date",Comparer.OrdinalIgnoreCase) then "Date" else "Payment"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Nature]), "Nature", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Payment instance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{{"Order Number", Int64.Type}, {"Order Date", type datetime}, {"Customer", type text}, {"Sales Rep", type text}, {"Payment", type number}, {"Date", type datetime}})
in
    #"Changed Type"

[parry2k]

@Willbw02 Indeed, it is all about preparing the data in a format that is scalable and can deliver the reports.

[parry2k · Accepted solution]

@Willbw02 yes, it will work with as many payment types, see attached, You can tweak the solution as you see fit. The core logic is in data preparation in the PQ.

PS: There are a few more tables in the attached pbix, just ignore those.

[parry2k]

@Willbw02  Is this what you are looking for?

[Willbw02]

Yes, @parry2k ,  Can you please share with me the steps you took to get here?   And... would your method/solution change if there were more than two payment types, like 4 or 5 different types?

[Willbw02]

Thanks so Much - super helpful!   So its a series of merged unpivots to get rid of all the cross-tabulated info.