1 Reply Latest reply on May 19, 2016 9:09 AM by Ben Young

# Level of Detail calculation not working

I have a question on level of detail reporting, and hoping someone can help me out.  See the attached workbook.

Basically, this is the data

 Date Location Incidents Census Injury 1/1/2000 A 1 100 NO 1/1/2000 A 1 100 NO 1/1/2000 B 1 200 YES 1/1/2000 B 1 200 YES

There are two locations, and each has two records.  Each incident has a record, and there is also a census record on both, because I can’t arbitrarily assign the total location census to one record.  I handle that later in the workbook.  There is also an Injury dimension.

I calculate a percentage of incidents as incidents/ {fixed [Location],[Date] : MAX([Census])}

The issue I am having is when I filter on INJURY.  The total census of both units should always be the denominator.  The incidents/numerator should filter, but the denominator shouldn’t.

So unfiltered you have

Incidents: (A) 2 + (B) 2 = 4

Census: (A) 100 + (B) 200 = 300

Calc: 4/300*10 = 13.3%

If I filter out the ‘No’ Injuries it should be

Incidents: (B) 2

Census: (A) 100 + (B) 200 = 300 (I want the total census of both units, even though I am filtering the numerator)

Calc: 2/300*10 = 6.67%

But what it actually does is

Incidents: (B) 2

Census: (B) 200

Calc: 2/200*10 = 10.00%

So the Fixed thing isn’t actually taking the total census.  It still filters out the entire unit.  Maybe I'm misunderstanding how the FIXED thing works...?

• ###### 1. Re: Level of Detail calculation not working

Hi Ernesto,

You are so close! You got the level of detail correctly for the location. But, for the total census count to work you need another level of detail calculation. As it stands now, the filter removes the other values from the view, leaving you with just the max of the census that still remains.

The way to work around the filter as well is to make a second level of detail calculation on your Census Max calculation using this syntax: { SUM([Census Max]) }. The LOD expression in this case, without any specified dimensions, returns the total value of the summed Census Max for the dataset, regardless of whatever filters you have on.

Attached is a workbook. Hope this helps!

1 of 1 people found this helpful