Aggregation with drilldown dimension in bar chart

[KDev1]

Hi. I am having trouble using aggregation function with a drilldown dimension in a chart.

I have the below table:

Category     Product     ID     Defect     DefectGrade
Vehicles      Car           123    Y            1
Vehicles      Car           345
Vehicles      Bus           445
Vehicles      Bike          457    Y           1
Vehicles      Bike          677    Y           2
Stationary   Pencil       244
Stationary   Pencil       767     Y           1

I have created a drilldown dimension of Category-->Product.
I want to use the drilldown dimension and the "DefectGrade" to calculate the number of defective products/total manufactured products in a bar chart.

Eg:
Out of 4 vehicles manufactured, 3 are defective --> 2 in grade 1 and 1 in grade 2. So the result would be:

Category     DefectGrade     DefectPercentage
Vehicle        1                       2/4=50%
Vehicle        2                       1/4=25%
Stationery   1                       1/2=25%
Stationery   2                        0/2=0%

When I drill down on the category, the aggregation should change to Product level.

Eg:
Out of 2 cars manufactured, 1 is defective --> 1 in grade 1 and 0 in grade 2. So the result would be:

Product     DefectGrade     DefectPercentage
Car            1                       1/2=50%
Car            2                       0/2=0%
Bus           1                        0/1=0%
Bus           2                        0/1=0%
Bike          1                        1/2=50%
Bike          2                        1/2=50%

I'm trying to use this formula

count(distinct {<Defect={'Y'}>} ID)/ Aggr(nodistinct count(distinct {<DefectGrade=>} ID),Product,Category)

The numerator gives me the correct value but the denominator works only with one dimension in aggregation. Could you please help me understand what I'm doing wrong? Thank you very much in advance.

[Kushal_Chawda · Accepted solution]

@KDev1  try below

count(distinct {<Defect={'Y'}>} ID)/ count(distinct total <Product,Category>{<DefectGrade=>} ID)

[KDev1]

Works like a charm! Thank you so much!!

[KDev1]

Just another behavior I noticed - the formula doesn't seem to work when we use a calculated dimension. I had to fix formatting issues in the Category field, so there was an expression in the master drilldown dimension initially. When I removed the expression, the formula worked as expected. Maybe a limitation? However, now I moved the formatting to the load script instead, so it took care of the issue. Thank you, again!

[Kushal_Chawda]

calculated dimensions are always problematic. It's always better to move conditions in script.