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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GrahamR99
Resolver I
Resolver I

Sales Data with Stock Booked in Data

Hello

I have a Power BI Project, with Query1 Sales Data this has;

Product_Name,Region,Area,Product_Range,Year,Week,Sale_QTY,Sale_Value

 

I have a Query2 Stock Booked in Data this has;

Product_Name,Region,Area,Product_Range,Year,Week,Book_QTY

 

I need to bring the two querys together and pivot them and perfom a calulation of booked in stock minus the sold stock.

 

My pivot table needs to look like this.

Pivot.jpgP

 

What are the steps I need to do to be able to do this?

 

I have tried to merge the queries together, but I have booked in counts when I don't have sales so the counts do not match up to what I am expecting.

 

Regards

 

GrahamR99

 

1 ACCEPTED SOLUTION
Abduvali
Skilled Sharer
Skilled Sharer

Hi @GrahamR99,

 

Try function called Append Queries in Edit Mode:

 

Table 1Table 1Table 2Table 2Go to any Table and click Append Queries, you can select Three or more and then manually bring missing TableGo to any Table and click Append Queries, you can select Three or more and then manually bring missing TableTables JoinedTables JoinedMerge 2 Region columns by selecting both, right click and select Merge columns and hit OKMerge 2 Region columns by selecting both, right click and select Merge columns and hit OKFinal result, now just drag and drop into your table matrix to completeFinal result, now just drag and drop into your table matrix to complete

 

 Capture26.PNG

 

 

Hope this helps.

 

 

Regards

Abduvali

 

 

View solution in original post

5 REPLIES 5
Abduvali
Skilled Sharer
Skilled Sharer

Hi @GrahamR99,

 

Try function called Append Queries in Edit Mode:

 

Table 1Table 1Table 2Table 2Go to any Table and click Append Queries, you can select Three or more and then manually bring missing TableGo to any Table and click Append Queries, you can select Three or more and then manually bring missing TableTables JoinedTables JoinedMerge 2 Region columns by selecting both, right click and select Merge columns and hit OKMerge 2 Region columns by selecting both, right click and select Merge columns and hit OKFinal result, now just drag and drop into your table matrix to completeFinal result, now just drag and drop into your table matrix to complete

 

 Capture26.PNG

 

 

Hope this helps.

 

 

Regards

Abduvali

 

 

Hi Abduvali

That is almost it, I can't get Region and Area to display on one pivot.

 

This is what I have;

Pivot2.jpg

How do I get it like your last image?

 

Regards

 

GrahamR99

Use Matrix table and expand:

Capture28.PNG

Hi Abduvali

How do I do a Calulated Field so I need to book_qty - SQTY?

 

I guess I can not do it on the Append Table as they will have different lines.

 

Regards

 

GrahamR99

Are you talking about sales quantity???

  • if so drag and drop your quantity into Values field and select SUM

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.