TOTALYTD with Categories doesn't correctly aggregate

[rerwin21]

I have a data set with the following format:

Category          Type         Date     Hours
A               Billable    1/1/2018    8
A               Billable    1/2/2018    10
A               Billable    1/3/2018    7
B               Billable    1/4/2018    8
B               Billable    1/5/2018    10
B               Billable    1/6/2018    7
Not assigned    Available   1/1/2018    8
Not assigned    Available   1/2/2018    8
Not assigned    Available   1/3/2018    8
Not assigned    Available   1/4/2018    8
Not assigned    Available   1/5/2018    8
Not assigned    Available   1/6/2018    8

I've got a cumulative calculation using TOTALYTD. The calculation is YTD utilization of a resource (i.e., Billable / Available). So, the formula is:

ytd_billable = TOTALYTD(Sum(Hours[Hours]),Hours[Date],Hours[Type]="Billable")
ytd_available = TOTALYTD(Sum(Hours[Hours]),Hours[Date],Hours[Type]="Available")
ytd_utilization = [ytd_billable] / [ytd_available]

Now, this works just fine when I plot this with date as the x axis and ytd_utilization as the values of the plot. However, when I add Category to the chart, I get infinity for each group rather than the value of ytd_utilization for each level of Category. How do I get the desired behavior? See graphs below for what I'm looking for (graphs are based different data, but same structure):

See this post for the images: my post on StackOverflow

[v-chuncz-msft · Accepted solution]

@rerwin21,

There's already an answer at https://stackoverflow.com/questions/49758356/totalytd-with-categories-doesnt-correctly-aggregate-pow....

[rerwin21]

I have a data set with the following format:

Category          Type         Date     Hours
A               Billable    1/1/2018    8
A               Billable    1/2/2018    10
A               Billable    1/3/2018    7
B               Billable    1/4/2018    8
B               Billable    1/5/2018    10
B               Billable    1/6/2018    7
Not assigned    Available   1/1/2018    8
Not assigned    Available   1/2/2018    8
Not assigned    Available   1/3/2018    8
Not assigned    Available   1/4/2018    8
Not assigned    Available   1/5/2018    8
Not assigned    Available   1/6/2018    8

I've got a cumulative calculation using TOTALYTD. The calculation is YTD utilization of a resource (i.e., Billable / Available). So, the formula is:

ytd_billable = TOTALYTD(Sum(Hours[Hours]),Hours[Date],Hours[Type]="Billable")
ytd_available = TOTALYTD(Sum(Hours[Hours]),Hours[Date],Hours[Type]="Available")
ytd_utilization = [ytd_billable] / [ytd_available]

Now, this works just fine when I plot this with date as the x axis and ytd_utilization as the values of the plot. However, when I add Category to the chart, I get infinity for each group rather than the value of ytd_utilization for each level of Category. How do I get the desired behavior? See graphs here for what I'm looking for.