Help in Creating Measure that calculates count of Employees based upon comparison of aggregate value

[HassanAshas]

Hi, I have a dataset as given below, 

Emp Code	Name	Allocation	Project Code
1	Matt	0.4	Bench
1	Matt	0.2	A
1	Matt	0.3	C
2	Avery	0.6	A
3	Aryub	1	C
4	Jacob	0.1	Bench
4	Jacob	0.1	C
4	Jacob	0.4	A
5	Zara	0.6	Bench
5	Zara	0.4	C

I need to calculate Two Measures (Values), which are as below, 

1. Non-Allocated Resources: Total Number of Employees who are more than 50% on Bench 
2. Allocated Resources: Total Number of Employees who are more than 50% on Projects other than Bench 

The problem that I am facing is I need to find the ratio of the aggregate values of Each of the Employees and based upon that ratio, calculate my DistinctCount. So, for example, 

If I am talking about Emp No. 1 - Matt 

For Emp Code: 1, Matt 

Allocation on Projects: 0.2 + 0.3 = 0.5 
Allocation on Bench: 0.4 
Total Allocation = 0.4 + 0.5 = 0.9 

So, to calculcate individual ratio on projects/bench,

Total Allocation on Projects = Total Allocation on Projects / Total Allocation 
Total Allocation on Projects: 0.5 / 0.9 = 0.55

Total Allocation on Bench = Total Allocation on Bench / Total Allocation 
Total Allocation on Bench: 0.4 / 0.9 = 0.44

Since Allocation on Projects > Allocation on Bench, so Emp Code is Allocated

I can find aggregate sum of one employee on Projects or on Bench, or can find his status (whether he is Allocated or not), but I need to create a measure that calculates the total number of employees who are allocated (that is, it needs to find whether person is allocated and then calculate the total count of all those employees who are allocated) 

So for the above given dataset, following would be the results, 

Emp Code 1 - Matt: Allocated
Emp Code 2 - Avery: Allocated 
Emp Code 3 - Aryub: Allocated
Emp Code 4 - Jacob: Allocated
Emp Code 5 - Zara: Non-Allocated

And the two required measures will be, 

1. Total Allocated Employees: 4
2. Total Non-Allocated Employees: 1 

Can anyone help in creating these two measures, please? Thank you! 

If you need Power BI File with the required data loaded, you may download from here: https://drive.google.com/file/d/1LNxlD7sqJ57XrccUjnTq8BVLVwsFkfJE/view?usp=sharing

[tamerj1 · Accepted solution]

Hi @HassanAshas 
Please refer to attached sample file with the solution

Total Allocated Employees = 
SUMX (
    VALUES ( Competency[Name] ),
    VAR CurrentEmpTable = CALCULATETABLE ( Competency )
    VAR OtherProjects = FILTER ( CurrentEmpTable, Competency[Project Code] <> "Bench" )
    VAR TotalAllocation = SUMX ( CurrentEmpTable, Competency[Allocation] )
    VAR OtherProjectsAllocation = SUMX ( OtherProjects, Competency[Allocation] )
    VAR PercentAllocation = DIVIDE ( OtherProjectsAllocation, TotalAllocation )
    RETURN
        INT ( PercentAllocation > 0.5 )
)

Total Non-Allocated Employees = 
SUMX (
    VALUES ( Competency[Name] ),
    VAR CurrentEmpTable = CALCULATETABLE ( Competency )
    VAR BenchProjects = FILTER ( CurrentEmpTable, Competency[Project Code] = "Bench" )
    VAR TotalAllocation = SUMX ( CurrentEmpTable, Competency[Allocation] )
    VAR BenchrProjectsAllocation = SUMX ( BenchProjects, Competency[Allocation] )
    VAR PercentAllocation = DIVIDE ( BenchrProjectsAllocation, TotalAllocation )
    RETURN
        INT ( PercentAllocation > 0.5 )
)

[HassanAshas]

@tamerj1  That worked like charm! Thank you soo much! 

But unfortunately, I don't really understand how is it working 😕 Possible for you to do a me another favor, if you maybe can, haha 😅
I will try to understand it on my own as well, meanwhile. 

Two things that are actually confusing for me in this, 

1. Why did you use Values (Competency[Name])? You could have simply used Competency, and it would have worked, right? 
   Or is it possibly have to do something with Code Optimization? (Rather than using the whole table, you use only one single column in which you are interested)? And if that's the case, then why did you not use Competency[Emp Code]? (I tried to use Emp Code, and it also gave me same results. Do you think that in a larger dataset, where there can be a duplicate name, using "Name" can be an issue? Or am I seeing it the wrong way?)
   
   
2. Second thing that is really confusing for me is the use of VAR CurrentEmpTable = CALCULATETABLE (Competency). You are basically copying the same table in the CurrentEmpTable? Why can't we use "Competency" in our next variables, that is 

    VAR BenchProjects = FILTER ( Competency, Competency[Project Code] = "Bench" )
    VAR TotalAllocation = SUMX ( Competency, Competency[Allocation] )​

I actually tried to use the original table and surprisingly for me, the answer was incorrect now. (It was giving accurate result in the Table, but for the overall value, answer was incorrect) 

That's really weird for me and I can't at all understand what's going on here. Can you probably clarify this a bit, if you have the time only to do so? 😐 I would be very thankful to you! 

And also, thanks a lot again for replying and solving the issue, by the way. It's a huge help. 

[tamerj1]

@HassanAshas 

Nice questions. Let me try to answer:

No, the iteration over the Competency is not helpful as it will iterate the rows of the table one by one and each row has only one project code while for each employee I have multiple project codes. Iterating over the VALUES ( Competency[Name] ) will give me the access to all the rows that belongs to the employee under iteration. All I need to do is to perform context transition and here the CALCULATETABLE comes to proof itself. CALCULATETABLE inside an iteration over the employee names will calculate the subset of rows that belongs to that employee. With that table in hand it is now clear what needs to be done next.

Actually this table is already available in the current filter context as we are slicing by employee name already. For each row in the table visual I have only the subset of rows that belong to the employee of the current filter context. Therefore, we can directly start from line 5 of the DAX code up to line 10 and it should work totally fine for all employees. However, it won't work properly in the total cell rather it will produce wrong result. And this is why we need to iterate over the VALUES ( Competency[Name] ).

And yes, as long VALUES ( Competency[Emp Code] ) has the same granularity it makes no difference of using either of them but Yes using VALUES ( Competency[Emp Code] ) is the best practice.

Removing CALCULATETABLE from the picture will have no effect on the result of each employee as the table existing in the current filter context is already filtered down to the subset of rows that belongs to each employee. However, at total level you need to force context transition to obtain correct result as illuistrated above.

[HassanAshas]

Thank you once again for such a detailed explanation! 
I need to look into Context Transition into more detail. This knowledge is something I am looking to gain now. Thanks a lot, I am really grateful for the help you have given! ❤️ 

[FreemanZ]

hi @HassanAshas 

how do you get this:

0.5 / 0.5 + 0.4 = 0.55 

?

[HassanAshas]

@FreemanZ  Ok, I am sorry. I made a bad formatting error. 

It is actually supposed to be, 

0.5 / (0.5 + 0.4) = 0.55 

or in other words => 0.5 / 0.9 = 0.55 

Basically, you are dividing Total Allocation on Projects / Total Allocation 

I have fixed the mistake, I am sorry for the confusion.