Sorting SUM to match an ID, multiply by value attached.

[ezedayplas]

Hello everyone, I am pretty new to power BI.

My challenge today is to get an added weight sold of different products, each with a different weight. I have a sales table where one of the columns is the quantity sold in one single sale (linked to a single product), which I have to compile for a determinated time frame to get the number of products sold. Then I multiply that number to the weight attached to the product in another table.

I was able to pull off doing this a product at a time (pretty simple really), but my objective is to add however many as I want at the same time, each one having its own correct sales-weight relation.

I have tried multiple things but I cannot seem to get it right.

[amitchandak · Accepted solution]

Can you share sample data and sample output?

But a few solutions which can help you without going into the details. get weight into sales table as a new column and use it. Or have a measure based on a common dimension.

new column in sales = sumx(filter(weight[product]=sales[product]),weight[weight])
new column in sales = maxx(filter(weight[product]=sales[product]),weight[weight])

Measure = sumx(summarize('product dim','product dim'[product], "_qty",sales[qty],"_wt",sum(weight[weight])),[_qty]*[_wt])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

G

[ezedayplas]

Thank you @amitchandak and everyone for your replies.

So each of the Sales table rows is an individual sale, each with its own quantity of products sold. I could make a calculated column to just have the weight of each sale in every row, which would most likely work. I haven't really worked with columns before, I'm trying to do Sales[units] * RELATED(Products_Weight[unitWeight]) but nothing really happened. The Sales and Products_Weight tables aren't directly related, they have another Dim_Products table in between, don't know if that is the problem.

[ezedayplas]

Ok, nevermind. Adding the weight of each sale to the Sales table actually worked, thank you very much, cheers!

[bfernandez]

Hey @ezedayplas 

Can you provide screenshots and what your expected outcome should be?

[smpa01]

@ezedayplas  would help if you can share some sample data (which can be copy pasted in excel) representative of your problem with the desired outcome clearly shown