Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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"
@Willbw02 Indeed, it is all about preparing the data in a format that is scalable and can deliver the reports.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Willbw02 Is this what you are looking for?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
Thanks so Much - super helpful! So its a series of merged unpivots to get rid of all the cross-tabulated info.
User | Count |
---|---|
85 | |
84 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |