I need help developing a measure that will perform a calculation if a condition is met. I have two tables I am working with. In Table 1, I am simply using the data to count the population based on a group ID. In Table 2, I have a list of professionals that support the population in Table1. However, some groups in Table 1 have multiple professionals supporting them, so each has a percentage of the group supported assigned. I am trying to create a measure that calculates the number of the population supported by each professional based on the percentage of the specific group they support. As an example:
Table1
GROUP | | EID |
1001 | | 1 |
1001 | | 2 |
1001 | | 3 |
1001 | | 4 |
1002 | | 5 |
1002 | | 6 |
1003 | | 7 |
1004 | | 8 |
1004 | | 9 |
1004 | | 10 |
Table2
Professional | GROUP ASSIGNED | % ASSIGNED |
Mary | 1001 | 50 |
John | 1001 | 50 |
Mary | 1002 | 100 |
John | 1003 | 100 |
Mary | 1004 | 33 |
John | 1004 | 33 |
Billy | 1004 | 34 |
I need the measure to calculate the total number of the population each professional supports based on their assigned group and how much of the assigned group that cover. Some professionals have multiple groups assigned.
In the example I provided, the output should be Mary supports (2 from group 1001 – count of EID in group 1001 * .50, 2 from group 1002- count of EID in group 1002 * 1, 1 from group 1004 – count of EID in group 1004 * .33) a total of 4.99 (based on count of EID).
Any help to solve is greatly appreciated….
Frank / Rookie Power BI (learning)
