Forum Discussion
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 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 |
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 resultBest Regards,
DangarIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- Dangar332Resident 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 resultBest Regards,
DangarIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.