Calculate response rate by use 2 slicers

[wongmeta]

Hi,

I have 2 files loaded to Power BI, one for result and another one for target then I would like to calculate the % response rate (count result/count target).  I selected card to show response rate, it's works when filter by country but when filter department the result was wrong.  Please advise any solution or idea to fix this.  Thanks

Noted : Country & department are many to many

[Anonymous]

Hi @wongmeta ,

Please create two middle tables first.

Table 3 = VALUES('Table 1'[Country])

Table 4 = VALUES('Table 2'[Department])

Create new relationships between tables and apply columns to filters.

Please check if the result is correct.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[wongmeta]

@Anonymous  Thanks for your advice.

I follow your instruction but still not working.  Seem response rate when filtered country or department are divided by total target not specific to country or dept target that I selected.  What's my mistake?

Below is my data model.

And 3 measures

test result = COUNTROWS('Survey result')

test target = COUNTROWS('SEA Target')

Test response rate = DIVIDE([test result],[test target])

Thank you very much!

[Anonymous]

Hi @wongmeta ,

Are there any unnoticed filters affecting the results? Consider using the ALLSELECTED expression to clear inner filters and keep outer filters. For example.

TOTAL RESPONSE = CALCULATE(COUNTROWS('Table 1'),ALLSELECTED('Table 3'),ALLSELECTED('Table 4'))

TOTAL TARGET = CALCULATE(COUNTROWS('Table 2'),ALLSELECTED('Table 3'),ALLSELECTED('Table 4'))

RESPONS RATE = DIVIDE([TOTAL RESPONSE],[TOTAL TARGET])

If this doesn't work for you, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

Also, attach the pbix file for reference.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[wongmeta]

@Anonymous  Hi

I've only 2 slicers in report and try to clean everything but still doesn't work.

Link to document file.

https://www.dropbox.com/s/pz2r8nrcobjg7mh/Sample%20data.pbix?dl=0 

Appreciated your helps.  Thank you very much!

[Anonymous · Accepted solution]

Hi @wongmeta ,

The field in the middle table should be applied to the filter, not the field from the original table.

Please check. Also, attach the pbix file for reference.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@wongmeta ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[wongmeta]

@amitchandak 

Response rate is depends on country and department.

below table for result

Country	Department	Company	Q1	Q2	Q3	Q4
ID	M	AbBbCcDd	4	4	4	3
ID	M	AbBbCcDd	4	4	4	4
ID	L	AbBbCcDd	4	4	4	4
ID	P	AbBbCcDd	4	4	5	4
ID	P	AbBbCcDd	4	5	5	4
SG	P	AbBbCcDd	5	5	5	5
SG	C	AbBbCcDd	4	4	5	5
SG	L	AbBbCcDd	4	4	3	4
SG	P	AbBbCcDd	3	3	2	4
SG	C	AbBbCcDd	4	4	4	4
SG	P	AbBbCcDd	4	4	4	4

And table for target

Country	Department	Customer IDH	Customer name
SG	M	xxxx	AbCDE
SG	M	xxxx	AbCDE
SG	L	xxxx	AbCDE
SG	P	xxxx	AbCDE
SG	O	xxxx	AbCDE
SG	C	xxxx	AbCDE
SG	C	xxxx	AbCDE
SG	M	xxxx	AbCDE
ID	M	xxxx	AbCDE
ID	L	xxxx	AbCDE
ID	L	xxxx	AbCDE
ID	C	xxxx	AbCDE
ID	P	xxxx	AbCDE
ID	M	xxxx	AbCDE
ID	L	xxxx	AbCDE
ID	P	xxxx	AbCDE

I created new measure to countrows of 2 tables and then use 

RESPONS RATE = DIVIDE('Survey result'[TOTAL RESPONSE],[TOTAL TARGET]) 

Results is works when filter country but missing when select department. 

Appreciated your advices