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.
Hi,
I am trying to get the total quantity of a table but the data itself has multiple duplicates.
On top of that, there is no distinct identifier as 1 lot ID has 2 values.
When i try to use distinct and sumx function, I get error that a single value for column "QTY" cannot be determined.
See example data below.
LotID | Date | Month | Year | QTY | MFGSite |
6380625.1 | Friday | September 15 | 2023 | 38461 | A |
6380625.1 | Friday | September 15 | 2023 | 6419 | A |
6380625.1 | Friday | September 15 | 2023 | 38461 | A |
6380625.1 | Friday | September 15 | 2023 | 6419 | A |
6390627.2 | Wednesday | September 13 | 2023 | 59800 | B |
6390627.2 | Wednesday | September 13 | 2023 | 59800 | B |
6390627.2 | Wednesday | September 13 | 2023 | 59800 | B |
6390141.2 | Wednesday | September 13 | 2023 | 10000 | B |
6390141.2 | Wednesday | September 13 | 2023 | 10000 | B |
6390141.2 | Wednesday | September 13 | 2023 | 10000 | B |
6390141.2 | Wednesday | September 13 | 2023 | 10000 | B |
6390627.2 | Friday | September 15 | 2023 | 50000 | B |
6390627.2 | Friday | September 15 | 2023 | 50000 | B |
6390627.2 | Friday | September 15 | 2023 | 50000 | B |
6390186.2 | Thursday | September 14 | 2023 | 2959 | B |
6390186.2 | Thursday | September 14 | 2023 | 2959 | B |
6390186.2 | Thursday | September 14 | 2023 | 2959 | B |
6390186.2 | Thursday | September 14 | 2023 | 2959 | B |
6394728.1 | Thursday | September 14 | 2023 | 5421 | B |
6394728.1 | Thursday | September 14 | 2023 | 5421 | B |
6394728.1 | Thursday | September 14 | 2023 | 5421 | B |
6394728.1 | Wednesday | September 13 | 2023 | 50000 | B |
6394728.1 | Wednesday | September 13 | 2023 | 50000 | B |
6394728.1 | Wednesday | September 13 | 2023 | 50000 | B |
Since 1 distinct lot ID has 2 values, I tried to concatenate the lot ID and quantity so that I can get 1 distinct lot ID+Qty.
Concatenate | Lot ID | Values |
6380625.138461 | 6380625.1 | 38461 |
6380625.16419 | 6380625.1 | 6419 |
6390627.259800 | 6390627.2 | 59800 |
6390141.210000 | 6390141.2 | 10000 |
6390627.250000 | 6390627.2 | 50000 |
6390186.22959 | 6390186.2 | 2959 |
6394728.15421 | 6390186.2 | 5421 |
6394728.150000 | 6394728.1 | 50000 |
Manually cleaning this up in excel, I should be getting the total value of 223060.
How can I do it in power BI via. Would like to know how to use the distinct and sumx syntax for these case.
Hi. You can solve the issue in Power Query. Just select all columns and click Remove Duplicates:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |