Ignore table dimension

[Lauri]

I suspect I'm going to have a "D'oh!" moment when someone gives me the solution...

I have a data table of patients with dimensions PatientID, AgeGroup.

I have a data table of conditions with dimensions PatientID, Condition. A subset of all patients are in it.

I created a straight table with dimensions AgeGroup and Condition; the measure is the % of all patients who have each Condition. For example, "Asthma" is a Condition, so the result should be:

AgeGroup	Condition	%ofPatients
0-17	Asthma	4%

The calculation is:

Count(distinct PatientID)/Count(Total <[Age Group]> Distinct PatientID)

My problem: When I uncheck 'Include null values' for Condition, the denominator includes only patients who have one of the conditions. But I need it to include ALL patients, including those not in the Conditions data table. I want to know what percent of all of the kids have asthma, what percent have diabetes, etc.

Am I forced to include null values for Condition for the calculation to be correct?

I tried replacing Total with All. Didn't help. I tried adding all PatientID values to the Conditions data table with a value of 'NA' in the Condition field. That works mathematically, but having a row for 'NA' is totally confusing for users.

[sunny_talwar · Accepted solution]

May be try this

Count(DISTINCT PatientID)/Aggr(Count(DISTINCT PatientID), [Age Group])

[Lauri]

Yes! I just had to add NODISTINCT to the Aggr function.

Thanks so much. (But I didn't slap my forehead with a D'oh! -- this wasn't obvious to me!)

[sunny_talwar]

Oh ya, I totally forgot to add the NODISTINCT in there.. good catch...