Using DAX to Sum then Divide text

[montyfern]

Hello! I'm stuck. Say I have a shelter full of dogs where I'm looking for the females in heat to quarantine them. I need to count the "0" age females, then divide that by the number of breeder status (females) for each location. I've written tons of counts, divides, even broke out calculate but am even more confused. THANKS SO MUCH!

 

[jorismoerings]

Hi,
try this:

Age Count :=COUNT( [Age] )
Age Count Is Age is 0 And Breeder Status is Female:= CALCULATE( [Age Count] , FILTER(Table, [Age] = 0) && [Breeder Status] = "Females" ))
Breeder Status is Female:= CALCULATE( [Age Count] , FILTER(Table, [Breeder Status] = "Females" ))

Measure asked for:= Age Count Is Age is 0 And Breeder Status is Female / Breeder Status is Female

The "per location part" is something to cover in the Power Pivot and the measures will re-adjust themselves

 

[montyfern]

Hi jorismoerings,

Thanks! You're very close. I didn't explain, basically diding the number of zero years of "age" animals by the number of breeder (status) females for each "location" as per data below. The first measure works beautifully; easure asked for:= Age Count Is Age is 0 And Breeder Status is Female / Breeder Status is Female But I'm stuck still showng the 0 age females to divide by location?

ID Number	Sex	Birthdate	Age	Current Location	Status
A21	Male	43654​	1​	AR1	Boarder

I have a bunch of different locations, from AR1 to AR13, & STATUS of boarder or breeder. Does this make sense? I can get the 0 age & female by your wonderful measure, I just can't show WHICH LOCATION has that prized data. I tried a divide 0 Age Female/Location by Location Count but I'm obviously not getting it as it's just 1's. Thanks!

 

[montyfern]

*DIVIDING not "diding, and "Measure asked for not easure

 

[jorismoerings]

Hi,

My answer relied upon the assumption of a field (named "Breeder Status") was available in your data.
In your data example i just see a field named status and it doesn't contain "Females" but "Boarder" which probably makes a lot of sense to you but not to me.

So probably my measure should have contained the field "Sex" instead of "Breeder status"

so try this for THE MEASURES:

1. Age Count :=COUNT( [Age] )
2. Age Count Is Age is 0 And Sex is Female:= CALCULATE( [Age Count] , FILTER(Table, [Age] = 0 && [sex] = "Female" ))
3. Sex is Female:= CALCULATE( [Age Count] , FILTER(Table, [Sex] = "Female" ))
4. Measure asked for:= measure 2 / measure 3

I dropped my testfile here.

See if this helps you forward.

 

[montyfern]

Ahh, yes there we go! The explicit aggregations are kickin' in w/ correct %'s. NICE! Will keep this open in case I did something wrong. Not familiar w/ FILTER...wow, very powerful. Calculate yes. MANY thanks for now Joris

 

[jorismoerings]

Ahh, yes there we go! The explicit aggregations are kickin' in w/ correct %'s. NICE! Will keep this open in case I did something wrong. Not familiar w/ FILTER...wow, very powerful. Calculate yes. MANY thanks for now Joris

No problem, glad I could help. Please remember, if the solution is successful, to mark the answer as the solution.

 

[montyfern]

Hello @jorismoerings,
Sorry to take so long! Have moved during this time, getting organized. Alas, the solution can't be marked b/c either I didn't understand it or person didn't explain it well. What he wants:
Total number of females breeders / total number of babies. What I did: (any advice?). THANKS!

 

[montyfern]

"Yes so breeder won’t be zero, they are not mature enough to breed until age 3-4, so generally speaking breeder females will be 4-15 yrs of age, only the new babies will be zero years of age, just born. Total number of females breeders / total number of babies. That help?"

 

[jorismoerings]

"Yes so breeder won’t be zero, they are not mature enough to breed until age 3-4, so generally speaking breeder females will be 4-15 yrs of age, only the new babies will be zero years of age, just born. Total number of females breeders / total number of babies. That help?"

was on my annual leave when your post came in hence the delay in answering:

So new babies as a measure should be: New babies:=CALCULATE( [Age Count] , FILTER(Table1, [Age] = 0 ))
and female breeders should be: Breeder Females:=CALCULATE( [Age Count] ; FILTER(Table1; [Sex] = "Female" &&[Age]>=4&&[Age]<=15))
asked Measure asked for:=IFERROR([New babies]/[Breeder Females] , 0)

something like this

testfile is here