Forum Discussion
Helper IDividing two fields and a filter
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!
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.
2 Replies
- amitchandak
Super User
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]))
- v-eachen-msft
Community Support
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.
