Response rate by sub department by survey cycle

[Anonymous]

I am able to create a response rate by number of responses/total invites. However, I was asked to filter it by sub department and by cycle.

I have 2 tables: one is a merged table from 6 survey cycles from MS Forms (all dummy data because surveys will be released next year). There's no total invites sent and only received data.

second is a table of sub departments of a Finance deparment, cycles, and total invites 

When i calculated my response rate, it does not filter by sub departments/work area but it gets its total from the Invited column. Here is my formula for the measure:

Response Rate =

    var responses = DISTINCTCOUNT('Combined data'[Merged])

    var total = SUM('Work Area'[Invited])

return

    DIVIDE(responses , total)

I also tried this but didnt work

Response Rate =

VAR responses = CALCULATE(DISTINCTCOUNT('Combined data'[Merged]), ALL('Work Area'))
VAR total = CALCULATE(SUM('Work Area'[Invited]), ALL('Combined data'))

RETURN
DIVIDE(responses , total)

My concern here is there's no table with uinque values because the one below here has to have a cycle indicated per work area (6 cycles total)

Work Area	Survey Cycle	Invited
AAA	Cycle 1	10
BBB	Cycle 1	19
CCC	Cycle 1	10
DDD	Cycle 1	9
EEE	Cycle 1	6
FFF	Cycle 1	7

[Anonymous]

Hi @Anonymous ,

Please try this measure:

Response Rate =

VAR responses = CALCULATE(DISTINCTCOUNT('Combined data'[Merged]), ALLSELECTED('Work Area'))
VAR total = CALCULATE(SUM('Work Area'[Invited]), ALLSELECTED('Combined data'))

RETURN
DIVIDE(responses , total)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.