Forum Discussion
Count values excluding duplicates
Hi,
As part of a separate issue, I had to split a column in my data set by delimiter. This has caused the duplication of much of the data by creating new rows.
E.g., I have Column A and Column B. Column A contains a Unique ID, and Column B contains a numeric value.
I have a visualisation which displays the sum of the values in Column B. However, after splitting the column, these values have been duplicated.
Is there a way to count the values in Column B, only at the first occurence of the Unique ID in Column A?
Column A Column B
1259 20
1395 138
1395 138
1395 138
1564 1
1695 50
1695 50
1695 50
1695 50
1695 50
I.E, I would want the sum of Column B to be 209, but it is displaying as 685.
Thanks in advance.
Hi Anonymous，
What you need is to create a measure as below:
Measure 2 = var _t=DISTINCT(ALLNOBLANKROW('Table'[Column A],'Table'[Column B])) return SUMX(_t,'Table'[Column B])Then you will see as below:
For the related .pbix file,you can turn to the URL below: https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EZTY-6ZX5_9HnH_p3Ix_qI0BzQF2i4CPRodwCIKZHmMVqg?e=WVFOjb
Best Regards,
Kelly
5 Replies
Anonymous ,
GO to Query Editor --> Select COlumn B --> right click and remove duplicate
Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!
Anonymous ,
Refer below screen shot for your reference and expected out value 209.
Power Query EditorExpected output
Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!
All the visualization in visualization pane you right-click and choose the first value.
Or Min agg should also work .
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 Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Hi Anonymous，
What you need is to create a measure as below:
Measure 2 = var _t=DISTINCT(ALLNOBLANKROW('Table'[Column A],'Table'[Column B])) return SUMX(_t,'Table'[Column B])Then you will see as below:
For the related .pbix file,you can turn to the URL below: https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EZTY-6ZX5_9HnH_p3Ix_qI0BzQF2i4CPRodwCIKZHmMVqg?e=WVFOjb
Best Regards,
Kelly
Hi Kelly,
I am new to Power Bi. Can you tell me how to add a measure. I am having a similar issue.
I have accounts payable data and would like to count the number of POs by company. However, I have multiple payments on a PO. Right now my visualization is counting the number of transactions, including duplicates. Can you help?
Thanks!
