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

[tomekm]

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 Employee ID	Selected Employee ID
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.

[VahidDM · Accepted solution]

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.

Appreciate your Kudos!!