Count values excluding duplicates

[Anonymous]

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.

[v-kelly-msft · Accepted solution]

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

[Anonymous]

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!

[amitchandak]

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-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Tahreem24]

@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!

[Tahreem24]

@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!