Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello. I am trying to pull data from data from 2 different tables into table.
1st table : ON HAND inventory which included all sales and purchase receipts. returns . gives me real on hand qty. I get my on hand after I sum all the transcations.
2nd table with just sales last 90D sales. monthly average (90d sales/3) with measure.
I want to build a table with 3 columns
1. items
2. on hand qty
3. sales Average
I am only only able to pull items with Summerize formula. How can I pull on final on hand qty by an item which will be sum of all tranctions in the 1st table and Average by an item from second table?
Thank you
Hi @Atinder
If both tables have the Item column, you can first group by "Item" column in the first table and add a new column by performing Sum aggregation on "On Hand Qty" column. Then use Merge Queries to merge the second table to the first table based on "Item" column. You will see a new table column. Click the icon on its column header and expand only "Sales average". Select "Sales average" column and divide it by 3 from Transform tab > Number column > Standard > Divide.
Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
I would not do this. You need 3 tables in your model:
Now, drop the item number from your Product table in a visual, and measures from both On Hand/Sales tables (those are FACT tables) in to the visual and you will get the desired results.
If you honestly need a table that is not going ot be used by Power BI, but you are using Power Query for excel and dropping data into the spreadsheet layer, we need sample data - good sample data with expected output because the M code will not be pretty and will not be done via the UI.
But this is not a good modeling technique. The Star Schema approach I mentioned at the top is the way to go for Power BI.
Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Importance of Star Schema
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.