Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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!
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
@Anonymous ,
Refer below screen shot for your reference and expected out value 209.
Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |