10 Replies Latest reply on Aug 26, 2016 10:21 AM by Nathan Graham

# LOD calculation not excluding filter selection

Hello,

I have a set of survey data. The end users want a flexible report that allows them to select the question and answers combination. The user wants to the percentage of times a question was answered X.That would mean,

The problem is the filters the user is using makes the "Total" part of the above calculation result change. I do not want the total calculation part of the formula to change based on the user selection. The total number of rows/records should not change. To get around this I've tried to use a Line of Detail (LOD) calculation for the Total.

{exclude [Sr Answer]:SUM([Number of Records])}.

This should provide me the total rows/records, ignoring the filter selection of the user. But its not....and I'm not sure what else is need to fix it.

Using a specific example from the twbx;

Filters: Complete Date = 7/1 -7/31/16, Question # = "4"

Tower DSS, has 4 survey's with a 'Sr Answer' of "1".

Tower DSS has 33 total surveys with a 'Sr Answer' of  "1" thru "5"

That is 4/33 = 12%. Or 12% of the surveys for Tower DSS, have an answer of "1"

Can anyone help me get the report to show the 4 and 33 at the same time, while also allowing the user to select multiple Sr Answers?

• ###### 1. Re: LOD calculation not excluding filter selection

Hi Nathan!

Thanks for a detailed explanation. Although, I am not sure where the value of 33 is coming from? Can you elaborate on that?

• ###### 2. Re: LOD calculation not excluding filter selection

Hello and Thank you,

The value of 33 is when ALL the 'Sr Answers' are selected on the filter.

• ###### 3. Re: LOD calculation not excluding filter selection

I see. Change the formula for the total rows to this:

{fixed [Question#], [Tower]: count([Sr Answer])}

Also, click on the dropdown menu of the date filter and select 'add to context'. That should do it:

1 of 1 people found this helpful
• ###### 4. Re: LOD calculation not excluding filter selection

Additionally, you can create another field for %:

[Count Sr Answer]/sum([total rows])

Right click on that calculated field > default properties > number format and change it to show 2 decimals for percentage:

Hope this helps!

• ###### 5. Re: LOD calculation not excluding filter selection

That's it, Thanks so much

• ###### 6. Re: LOD calculation not excluding filter selection

Pooja, one more thing.

I need the total to only be for the month.

For example the total of 33 is only for July, June's total is 16.

4/33 = 12% - July

1/16 = 6% - June

etc

• ###### 7. Re: LOD calculation not excluding filter selection

Because date filter is added to context, you will get the values for a month that is selected in the filter. If I change that to June, I indeed get the value you are looking for:

• ###### 8. Re: LOD calculation not excluding filter selection

Is there a way to alter the formula so I can see multiple months and monthly totals at the same time? The way it is set up now, if multiple months are selected, the total is for all the months.

My end user wants to view 13 months. I'll need the total for each month to calculate the percentage.

Thanks for the help

• ###### 9. Re: LOD calculation not excluding filter selection

Remove the completed date from the filter. Right click on the completed date field in the dimensions pane > create > custom date. In the pop up that opens do this:

Then include the custom date in the 'total rows' calc:

{fixed [Question#], [Tower], [Completed Date (Month / Year)]: count([Sr Answer])}

Now you see 16 for June and 33 for July. Is this what you mean?

• ###### 10. Re: LOD calculation not excluding filter selection

Bingo! Yes, that is it. Thank you for all your help.