Divide Sum of column A by unique value from cloumn B

[JoaoFidalgo94]

Hi everyone!

I'm new to power query and power bi and I'm having trouble finding a solution to this situation.

I need to obtain a percentage of quantity received per product, in which we have the quantity received column (which is being updated and creating new lines as we receive orders) and the total parts column which, as the lines are generated, the value is also being doubled.

My problem is that I only want to take into account one value from the total parts column and not the sum of them.

Produt	Quantity received	Date received	Total Quantity by Product
A	7	01/09/24	15
A	3	02/09/24	15
A	5	03/09/24	15
B	1	04/09/24	12
B	1	05/09/24	12
B	10	06/09/24	12

Could you help?

Regards,

Joao Fidalgo

[JoaoFidalgo94]

Hi @RichardJ ,

Thank you for your help! 

I think you gave me another way of do it that i didn't know it... which is great!

I tried your way and worked well, but before i found this way that i will share:

Measure, for total quantity for each produt: 

SUMX(GROUPBY('Table','Table'[Product],'Table'[Total Quantity]),[Total Quantity])

and then of course: 

% of received parts: 

DIVIDE(SUM(Table[Quantity received]),[Measure],1)

And worked also for what i wanted. Anyway thanks to you now i know another way! 😄

Best regards,

João Fidalgo

[RichardJ · Accepted solution]

Hi @JoaoFidalgo94 ,

If you use the Group By Function in Power Query
How to GROUP BY or summarize rows - Power Query | Microsoft Learn

you can 'Group By' the 'Product' and during the same operations
Sum the 'Quantity Received' - this will total the Quantity Received per product A, B
Max the 'Total Quantity By Product' - this wil show the Max Total Quantity By Product for A,B

As more rows are added the group by will update when the model is refreshed.

This table would look like
Product, Quantity Received, Total Quantity By Product
A, 15, 15
B 12, 12

You could then add a column to divide 'Quantity Received' by 'Total Quantity of Product'

Does this answer your question?