Forum Discussion

mrsbusch's avatar
mrsbusch
Regular Visitor
1 year ago
Solved

Aggregated Sum over Two Columns

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 IDAnimal IDVaccinesVaccines (Sum)Given Total by VetDesired Total by Vet
54512300182
6511231111
54512316182
54512316182
6511230011
6512341122
6512341122
  • 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.

2 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar

    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.

  • 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?