cancel
Showing results for
Did you mean:
Helper I

## Calculate overall ratio based on selected filter, including null values.

I’m trying to calculate a ratio for each employee ID, computed as the count of times they were selected for a project, divided by the count of times they were invited to a project.

Each employee ID’s ratio should be displayed when his employee ID is selected in a filter (and the overall ratio score to be shown in a score card or pie chart). Scenario below:

Employee ID ‘abc’ was invited to 2 projects. He wasn’t selected for project 1, so his ratio here is 0/1, but he was selected for the second project, so his ratio here is 1/1. Combined ratio should show 50% for this employee.

Here’s the sample data.

 Project Name Invited EmployeeID Selected EmployeeID project 1 abc xyz project 1 abc def project 1 xyz xyz project 1 xyz def project 1 def xyz project 1 def def project 2 abc abc project 2 xyz xyz

 Invited Employee ID - filter: abc

Desired output:

 Overall Ratio: 50%

Thank you.

1 ACCEPTED SOLUTION
Community Champion

Hi @tomekm

Try this measure:

``````Overall Ratio =
VAR _SelINID =
SELECTEDVALUE ( 'Table'[Invited Employee ID] )
VAR _Inv =
DISTINCTCOUNT ( 'Table'[Project Name] )
VAR _Select =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Project Name] ),
FILTER ( ALL ( 'Table' ), 'Table'[Selected Employee ID] = _SelINID )
)
RETURN
_Select / _Inv``````

Output:

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

Community Champion

Hi @tomekm

Try this measure:

``````Overall Ratio =
VAR _SelINID =
SELECTEDVALUE ( 'Table'[Invited Employee ID] )
VAR _Inv =
DISTINCTCOUNT ( 'Table'[Project Name] )
VAR _Select =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Project Name] ),
FILTER ( ALL ( 'Table' ), 'Table'[Selected Employee ID] = _SelINID )
)
RETURN
_Select / _Inv``````

Output:

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

Announcements