Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
Solved! Go to 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.
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?
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
16 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
22 | |
11 | |
10 | |
10 | |
8 |