Need to get a visual that shows the ratio of matching rows from 2 different tables

[ditka78]

I feel like this is a lot easier than I'm making it, but everything I try gives different results and I'm not sure what I'm doing wrong.

I have two tables Incidents and Agency Number Reference.

The Incident table has Incident tickets listed by the user they were opened for.  I have also merged it so the table has a column indicating an agency number the user is with. So for example:

User ID	Incident Number	Agency Number
12345	INC123	A123
67890	INC456	A456

The Agency Number Reference has all agents listed by their agency number so for example:

User ID	Agency Number
12345	A123
67890	A456
13579	A123

Not all users will have an entry on the Incident table.
I am trying to generate a visual that will allow me to show the Ratio of entries matching each agency number from each table.  So it would show (Incs in A123)/(Agents in A123).  So for the sample data above it would give me a bar graph showing a value of .5 for a123 and 1 for A456. 

I had a similar problem, where I was looking at the operating system used,that I solved by making a measure for each possible value of the common column that gave the count matching, 

Android Device Count = COUNTROWS(CALCULATETABLE('Devices','Devices'[OS]="Android"))

but that isn't practical here as there are hundreds of different values for the Agency Number.

[v-binbinyu-msft · Accepted solution]

Hi @ditka78 ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR cur_an =
    SELECTEDVALUE ( 'Agency Number Reference'[Agency Number] )
VAR tmp1 =
    FILTER (
        ALL ( 'Agency Number Reference' ),
        'Agency Number Reference'[Agency Number] = cur_an
    )
VAR tmp2 =
    FILTER ( ALL ( Incident ), Incident[Agency Number] = cur_an )
RETURN
    DIVIDE ( COUNTROWS ( tmp2 ), COUNTROWS ( tmp1 ) )

2. add a cluster column chart with field and measure 

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ditka78]

Thank you, I had tried basically this, but the Selectedvalue was the function that I was missing to iterate for each agency number.  This is working for what I needed.

[Manoj_Nair]

@ditka78 - pls try this DAX M1, screen shoot below, you can see the ratio calculated in M2

If this post helps you to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/