Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
niels_vngi
Frequent Visitor

Compare counts from two different tables

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.

 

niels_vngi_0-1652967753142.png

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

1 ACCEPTED SOLUTION
daniel79
Resolver II
Resolver II

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")))

View solution in original post

2 REPLIES 2
niels_vngi
Frequent Visitor

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
Resolver II
Resolver II

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")))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.