Dividing two fields and a filter

[Pillsbury33]

Hello everyone,

I’m hoping someone can help with my question. I am trying to divide two calculations in two separate tables with a visual filter. The logic I am looking for is effectively is to create a card visual that shows the following:

Measure = count of incidents [Table 1 Count of incidents] by [Table 1 Site] – this will be the report filter, divided by [Table 2 Sum of employees] for [Table 2 Site].

Somehow I need to match the site names from the two fields (they will have the same names) to divide the number of incidents (table 1) by the number of employees for the site (table 2).

Another solution that I couldn’t get to work was to add a new column to table 1 with the sum of employees for the table 1 site name.

I hope this is enough information to determine a solution. If not let me know what else I need to provide.

Table 1Table 2Expected outcome

Thanks!

[v-eachen-msft · Accepted solution]

Hi @Pillsbury33 ,

Firstly, you need to create a relationship between two tables in the model tab if you don't have it.

Then refer to the following DAX:

Measure =
CALCULATE (
    SUM ( 'Table'[Incident] ) / SUM ( 'Table (2)'[employee] ),
    ALLEXCEPT ( 'Table', 'Table'[Site] )
)

Here is the result.

Here is my test file for your reference.

[amitchandak]

@Pillsbury33 , Not sure I got. You need to create a common Site table/dimension and join with both tables.

Then you can create % = divide(Sum(Table1[Incident]),Sum(Table2[Employee]))