Aggregated Sum over Two Columns

[mrsbusch]

I have been struggling to find the total sum of one column grouped by conditions of other two columns.

For privacy purposes, let's say it's a veterinary dataset (it's not). Each row is for a specific vet visit for a specific animal. There are columns for specific veterinarian, animal ID, and a column that is 0 if no vaccines are administered and 1 if vaccines are administered that visit. I want to find out how many vaccine visits each animal has had by each veterinarian. I have tried a few different solutions, and I am always getting the wrong total for the "Total by Vet" col.

Note that the data is dynamic. 

Below is a sample of what the data would look like and what the "Total by Vet" column should be.

In my troubleshooting, I noticed that if I make the Vaccines column a sum, for some Animal IDs I still get 0/1. For those entries, the Total by Vet col looks correct. However, for some Animal IDs, I get the vaccines sum to be some large number (5,8,9, etc) that I can't decipher where it is calculated from. But, when I multiply that large number by the appropriate number of entries, I get the incorrect Total by Vet.

Total by Vet = SUMX(FILTER(Vax,Vax[Animal ID]=EARLIER(Vax[Animal ID]) && Vax[Vet ID]=EARLIER(Vax[Vet ID)),Vax[Vaccines])

Thank you in advance!

Vet ID	Animal ID	Vaccines	Vaccines (Sum)	Given Total by Vet	Desired Total by Vet
545	123	0	0	18	2
651	123	1	1	1	1
545	123	1	6	18	2
545	123	1	6	18	2
651	123	0	0	1	1
651	234	1	1	2	2
651	234	1	1	2	2

[FreemanZ]

hi @mrsbusch ,

your code shall be OK for a calculated column, if you expects a measure, then try like:

Total by Vet = SUM(Vax[Vaccines])

or?

[Dangar332 · Accepted solution]

Try below measure 

column =
        VAR Curr_vet = Vax[Vet ID]
	VAR curr_animal_id = Vax[Animal ID]
	VAR result = SUMX(
		FILTER(
			Vax,
			Vax[Vet ID] = Curr_vet && Vax[Animal ID] = curr_animal_id
		),
		Vax[Vaccines]
	)
	RETURN
		result

Best Regards,
Dangar

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