Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KA95
Helper III
Helper III

I want my SUMX measure to only sum up one column or the other (avoid duplicates)

Hi there, I have a simple measure that doesn't count duplicate values, however, it's not working as I originally anticipated. 

Below I have a matrix visual of the data I want to visualise correctly. The main two columns here are [LineItem Type] which show Assembly and Workshop (these are the two values that I definitely want to avoid duplicates for), then there is the [Year Groups] which show 7, 8, 9, here I want them to be summed up. And the values are [Quantity], so Year Group data I want added up, LineItem type I don't want duplicated, I only want it to sum up one LineItem type or the other. 

 

KA95_0-1653407207330.png

 

On this visual we have "Workshop" and "Assembly" figures. What I want to do is, is if a workshop or assembly figure is the same on the year group, I only want it to count one value, otherwise, it's a duplication (this is what I want to avoid). However, I only wanted this to happen for LineItem Type (Assembly & Workshop), not the year groups (7,8,9).

As you can see on the above visual, there is 220, 220 and 180 on line 9 under Assembly. It's not adding up correctly, it's only adding one 220 and 180 together to get 400, I want it to be 620. And for the workshop, I don't want that value to count because it's clearly a duplicate on the year 7 group (so that is correct). 

Here is my current measure below:

 

Number of Students = SUMX (DISTINCT('LineItem'[quantity]), FIRSTNONBLANK ('LineItem'[quantity], 0 ))
 
Does anyone know how I can fix this so that it matches my requirement or at least give me some pointers?
4 REPLIES 4
amitchandak
Super User
Super User

@KA95 , Try

Number of Students = SUMX (DISTINCT('LineItem'[quantity]), calculate(FIRSTNONBLANK ('LineItem'[quantity], 0 )))

 

better option

sumx(summarize('LineItem','LineItem'[quantity]),quantity)

Hi @amitchandak 

Thank you for your reply, I've tried both measures but unfortunately neither made a difference. 

KA95_0-1653466076334.png

 

I just need the values to be counted on one side or the other, so here for example, I would like it if Assembly or Workshop was counted so that it was 540, instead of 180. And at the same time I don't want the other side counted (which would be Workshop on the right hand side because they are duplicates).

The reason I'm doing it this way is because that's how the data is inputted so I'm trying to use a measure to fix it. Let me know if you need anything else Amit, thank you for your effort. 

 



Hi @KA95 

I'd like to help you, but this problem cannot be reproduced in my side because of missing data. Please copy the pbix file and import some dummy data to replace the original data, reduce the number of rows to provide a representative sample, and then use it as an example to provide your expected results.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xiaotang 

Sure I can send you over a demo I have here. 

https://www.dropbox.com/s/t772upltvzxz3vf/Power%20Bi%20Sample.zip?dl=0

 

Here is a ZIP of the sample file, i've tried to create it as similar as I can, with two sample datasets. 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.