Compare counts from two different tables

[niels_vngi]

Dear all,

We're working on a large household survey that is aimed to map the priorities of citizens. We store general information like age, gender, source of livelihood, etc in one table and store a maximum of 6 predefined priorities in another table. The two tables are linked on index number and parent index number.

Because our data is not terribly complex the visualisation of the survey goes quite well. There is just one (I believe rather simple) thing that I cannot get to work. I'm hoping there is anyone that can be of assistance, many thanks in advance.

We use a simple count to get an overview of the top priorities. The top priorities are visualized in both a bar graph and table. I've added a screenshot of a simplified version (left out the bar chart) of our report below.

What I would like to see in the data table is how many households have selected a specific priority in comparison to the total number of households that were willing to partake in the survey.

In the example above we've started 238 surveys in a particular municipality. Of the 238 surveys we started, 198 people were willing to coorporate. These 198 people selected the priority 'Frequent interruption of water' 153 times. In the same table that shows how often the priority was picked I would also like to display what percentage of househoulds picked this particular priority. In this case 153/198*100 = 77%

We have a report level filter on the woreda (municipality), if this filter is changed or additional filters from other values in the table are set, the percentage should update accordingly.

Both the .pbx file and the sample data can be found via this link:

https://drive.google.com/drive/folders/1vPejsnI4KkIgmVr5djWsEgZ-Ba46nnPH?usp=sharing

Again, many thanks in advance.

Best,

Niels

[niels_vngi]

Dear @daniel79,

First of all my apologies for my late response, there was some off time in between. Secondly, thank you very, very much for providing this solution. Works like a charm! 👌

All the best and kindest regards,

Niels

[daniel79 · Accepted solution]

You can try something like this

Households% = 
if(
countx(priorities,priorities[What is the priority?])>countrows(filter(ALLSELECTED('CRC household survey'),'CRC household survey'[B00 Are you able to conduct the survey at this household?]="Yes")),1,
countx(priorities,priorities[What is the priority?])/countrows(filter(ALLSELECTED('CRC household survey'),'CRC household survey'[B00 Are you able to conduct the survey at this household?]="Yes")))