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

[Anonymous]

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. 

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?

[amitchandak]

@Anonymous , Try

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

better option

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

[Anonymous]

Hi @amitchandak 

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

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. 

[v-xiaotang]

Hi @Anonymous 

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.

[Anonymous]

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.