Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
P
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
Solved! Go to Solution.
Hi @GrahamR99,
Try function called Append Queries in Edit Mode:
Table 1
Table 2
Go to any Table and click Append Queries, you can select Three or more and then manually bring missing Table
Tables Joined
Merge 2 Region columns by selecting both, right click and select Merge columns and hit OK
Final result, now just drag and drop into your table matrix to complete
Hope this helps.
Regards
Abduvali
Hi @GrahamR99,
Try function called Append Queries in Edit Mode:
Table 1
Table 2
Go to any Table and click Append Queries, you can select Three or more and then manually bring missing Table
Tables Joined
Merge 2 Region columns by selecting both, right click and select Merge columns and hit OK
Final result, now just drag and drop into your table matrix to complete
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;
How do I get it like your last image?
Regards
GrahamR99
Use Matrix table and expand:
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???
User | Count |
---|---|
136 | |
73 | |
72 | |
56 | |
55 |
User | Count |
---|---|
199 | |
95 | |
63 | |
62 | |
51 |