Dealing with Distinct Counts in OLAP. The "Distinct Count" problem is also known as the "many-to-many" problem. EG:
My data set involves health care. Some of the data involves patients, the different diagnoses they've had, and charges for visits. A typical view might look like this:
|Injury and Poisoning||$100||5||2|
Note that the Charges add up (100+200+300+300 = 900), and Patients add up (5+5+8+12 = 30), but the Patients don't (2+2+4+3 != 5). This is correct because there were only 5 patients and some patients had multiple diagnosis. EG: There were only two patients diagnosed for Mental Disorders: Sam four times and Sue once.