Calculating a percentage from two datasets in SSRS

Question

I am having trouble calculating the percentage of positive answers to some survey questions on my report.

I have two data sets; one which lists each organisation and how many surveys they have completed and the second one which uses exactly the same query but adds a filter for whether the questions have been answered as 'YES'.

With 2 tablix's on my report I get two lists which are grouped by organisation and which show overall surveys answered on one table and positive answers in the other table.

What I need to be able to do however, is to create a percentage of how many positive answers each organisation has completed.

I have tried using a lookup but I seem to be getting the total of ALL organisations in each row instead of each organisation in each row.

Maybe I am going about this the wrong way but any assistance you can give would be greatly received.

I am using VS2013 using SSRS 12.0.2344.23 on a local SQL2014 database.

Thanks, Alan

EDIT: Here is my current report layout: 1st Tablix is linked to the 'PositiveAnswers' dataset
Organisation Name | Number of Surveys with positive answers
[OrgName] | CountDistinct([Learner_ID])

I then have another tablix which is linked to the 'AllSurveys' dataset
Organisation Name | Total Number of Surveys
[OrgName] | CountDistinct([Learner_ID])

The ONLY difference between the two datasets is filtering for positive answers.

I would like to find out the percentage of positive answers for each organisation but when I add a new column to the 1st tablix with the following expression it doesn't work as expected: =CountDistinct([Learner_ID]) / CountDistinct(Lookup(Fields!Learner_ID,Fields!Learner_ID,Fields!_LearnerID,"AllSurveys"))

The Learner_ID field is what links the two datasets.

Thanks, Alan

Answer 1

Conditionally sum it up I think. Assuming your field is called answer and your dataset called ExampleTable, here is what a cell might look like:

=Sum(Iif(Fields![answer].Value="Y",1,0))/CountRows("ExampleTable")