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
Tuvdata
Frequent Visitor

How to combine sum of values of products in Power query editor?

Hello everyone. 

 

I am trying to create a inventory turnover dashboard. I have combined two queries (sales query and WH inventory query).

What I am trying to do is to divide average inventory quantities with monthly sold-out quantities. 

Problem: products are duplicated by each unique sold out values. Produst are duplicated so average inventory value is duplicated with it too. so it is giving me a wrong value. 

 

What I want: 1 line of each product per month. So I can divide average inventory with sales. 

 

attachment: this is the screenshot. 

  • Yellow cricles are the queries that I have combined
  • Red circle are highlighting the problem that I am facing
  • Right side red circle shows the steps that I have done

111_LI.jpg

 

I have tried 1:  measure. Measure is giving me what I want but I can not categorize my products by their Inventory turnover values. 

I have tried 2: to search this problem from internet but I couldnt find a solution regarding to my problem. Or I can not find the exact key word to search regarding this problem

 

Any help would be appreciated. Thank you

5 REPLIES 5
amitchandak
Super User
Super User

I am really not sure it a good idea to combine these two tables. You should analyze them with a common dimension in visualization

 

Refer : https://docs.microsoft.com/en-us/power-bi/guidance/

Thank you for your response. 

 

But would it be possible to do this by combining them ? 

GROUPBY and calculate average? But it will be slow if you have large data set. Go with DAX is a better choice.

I did group by. but that group by is the one letting them duplicate based on its sales values. 

But do you think it would be possible to show them without getting it duplicated? (even though it is slow)

 

the only reason I am doing this is I want to categorize the products based on its values. 

For example. 40% of our inventory will be sold in 30 days. 20% of them will sold in 60 days. 10% of them will be sold in more than 1 year (so we need to give promotion on these items) etc. . . 

 

Thank you for your response. I will look for DAX

Maybe you can share the M code? See how it goes there

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.