Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey guys,
So, I am working with some purchasing data and need help fixing/conslidating the data. For each Purchase Order (PO) we will have multiple lines for different items, and each line can have multiple deliveries. The issue is that the data shows the qty ordered as the total for each line and does not split it up even when there are multiple deliveries.
Is there a way in PBI to consolidate the qty ordered based upon the PO # and the Line #?
See example:
Delivery PO Line Product Vendor Qty Ordered Qty Received
1233 1111 1 X ABC 1000 500
1233 1111 1 X ABC 1000 500
1244 1111 2 Y ABC 300 100
1244 1111 2 Y ABC 300 150
1255 1111 2 Y ABC 300 50
Right now, a sum will show that we ordered 2000 of product X and 900 of product Y when I need PO 1111 Line 1 (product X) to show a total qty ordered of 1000 and Line 2 (Product Y) to show a total qty ordered of 300. Note that there are multiple POs for different companies/items and thus averaging will not work when there are mulitple POs/deliveries that have the same item.
I would like to be able to show the total qty ordered from each company compared to the quantity received.
Thanks!
Solved! Go to Solution.
Not sure if this is what you had in mind, but you can group by PO and Line and get the Max of Qty Ordered and the Sum of Rec'd:
Not sure if this is what you had in mind, but you can group by PO and Line and get the Max of Qty Ordered and the Sum of Rec'd:
Hey Nick,
Thanks for that! That is solving the issue I was having, but unfortunately another one has come up. Here's where I stand:
I had to create a separate data table (reference) from the original one, because it wouldn't let me add another column to the original data source and I needed the other data that wasn't included in the grouping. So, I did so and I have it working, but when I create slicers to try and filter the new grouped data I am having some issues.
We have multiple dates listed on the original data (Date they told us it would arrive and the date it actually arrived), and I need it to filter the POs by the date that it arrived. Unfortunately, it seems that when I connect the two data tables, it automatically connects it with the date that they told us it was going to arrive that is in the original data source. Is there a way to change this?
Thanks!
Hey,
I am assuming your table is set up as follows, using PO -> PO as a relationship.
In that case, click on your slicer and ensure that you have Arrival Date in your field box, rather than Scheduled Date.
Please let me know if your setup is different than this.
Alex
Hi Alex,
Thanks again for your help with this. Yes, I have the relationship set up as PO # --> PO #. Also, I do have the field set up to the received date and it seems to work for most, but not all of the POs.
In the example below, PO 219702, line 2, was received in on 8/20/2018, but when I filter it by the month and year (two separate slicers), it is including it with July. Not sure what is going on here...
Hey,
Looking at your data, it appears there should have a relationship from PO# to PO# and Line# to Line#. How are you handling this? I suspect you will find an error in there somewhere because Power BI doesn't support simultaneous relationships to my knowledge.
Further to this, you must have more than one date for each PO and Line combination because there is also a Delivery ID involved. From your raw data, I could expect to see different delivery dates for PO 1111, Line 2, Delivery 1244 and PO 1111, Line 2, Delivery 1255, for example.
Without seeing your complete data model I am making some assumptions here!
If your data was initially stored together, I would suggest using that. The diagrams in the image below could have been obtained using your raw data just as easily as after the grouping that you initially requested.
I understand this doesn't answer your question. If you can post a comprehensive data model, possibly in a pbix or pbit file with a small amount of dummy data, I would take a look at it for you.
Alex