Forum Discussion
How to Compute Values if one lot ID has multiple values
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.
1 Reply
- ERD
Community Champion
Hi. You can solve the issue in Power Query. Just select all columns and click Remove Duplicates:
