Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conslidate based upon two unique ids

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

Group in PQ.png

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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:

Group in PQ.png

Anonymous
Not applicable

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!

Anonymous
Not applicable

Hey,

 

I am assuming your table is set up as follows, using PO -> PO as a relationship.

 

PO_relations.png

 

In that case, click on your slicer and ensure that you have Arrival Date in your field box, rather than Scheduled Date. PO_slicer.png

 

Please let me know if your setup is different than this.

 

Alex

Anonymous
Not applicable

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... 

 

Power BI Snip.PNGHere is the raw data, as you can see line 1 of this PO should be included, but line 2 should not.Here is the raw data, as you can see line 1 of this PO should be included, but line 2 should not.

 

 

Anonymous
Not applicable

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors