Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks!
Solved! Go to 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.
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.
@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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |