Aggregated Sum over Two Columns

mrsbusch — Tue, 03 Dec 2024 18:44:04 GMT

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

Re: Aggregated Sum over Two Columns

Dangar332 — Tue, 03 Dec 2024 20:26:00 GMT

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.

Re: Aggregated Sum over Two Columns

FreemanZ — Tue, 03 Dec 2024 21:34:19 GMT

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?

topic Re: Aggregated Sum over Two Columns in DAX Commands and Tips

Aggregated Sum over Two Columns

Re: Aggregated Sum over Two Columns

Re: Aggregated Sum over Two Columns