ERROR in Bar Graph: DIVIDE scalar expressions have to be Aggregation Functions over CurrentGroup()

[HassanAshas]

Hi, let me first provide a bit of the background of what I am trying to solve, 

I have a dataset of employees working on month-to-month basis, 

Emp Code		Month	Allocation	Project Code
1	Umair	Aug-22	0	Bench
1	Umair	1-Sep	0	Bench
1	Umair	Oct-22	120	Bench
1	Umair	Nov-22	160	Bench
1	Umair	Dec-22	168	Bench
1	Umair	Aug-22	184	A
1	Umair	1-Sep	0	A
1	Umair	Oct-22	0	A
1	Umair	Nov-22	0	A
1	Umair	Dec-22	0	A
2	Avery	Aug-22	184	Bench
2	Avery	1-Sep	0	Bench
2	Avery	Oct-22	0	Bench
2	Avery	Nov-22	0	Bench
2	Avery	Dec-22	0	Bench
3	Aryub	Aug-22	184	Bench
3	Aryub	1-Sep	168	Bench
3	Aryub	Oct-22	154	Bench
3	Aryub	Nov-22	120	Bench
3	Aryub	Dec-22	166	Bench
4	Failak	Aug-22	24	Bench
4	Failak	1-Sep	0	Bench
4	Failak	Oct-22	120	Bench
4	Failak	Nov-22	160	Bench
4	Failak	Dec-22	88	Bench
4	Failak	Aug-22	164	C
4	Failak	1-Sep	166	C
4	Failak	Oct-22	40	C
4	Failak	Nov-22	0	C
4	Failak	Dec-22	88	C

In this dataset, the "Allocation" column means the Hours Employee has worked in that specific month on that specific project.

What I need to do is find the Utilization Efficiency of all the employees. The Utilization efficiency has a simple formula, 

Utilization Efficiency = Total Hours Worked in a Month / Total Working Days in that Month

So, that is, If I am talking about August 2022, and for Emp ID: 4

Total Working Hours = 184 (found using NETWORKDAYS)

Emp ID 4 working hours = 164 (on Project C) + 24 (on Project Bench) = 188 Hours

Emp ID 4 Utilization Efficiency = 188 / 184 = 1.02 

I have made a DAX that is able to do the above for me. The DAX is as follows, 

Aggregate_Working_Hours = 

VAR __Working_Hours = NETWORKDAYS(MAX(Competency[Date]), EOMONTH(MAX(Competency[Date]), 0)) * 8

VAR __Table = 
        GROUPBY(
            'Competency',
            Competency[Emp Code],
            "__Allocation",SUMX(CURRENTGROUP(),DIVIDE((Competency[Allocation]), __Working_Hours, 0))
        )
    VAR __Result = SUMX(__Table,[__Allocation])
RETURN
    __Result

Please note that I have a Single Select on the Months Slicer, and hence this DAX works for and finds the correct results for me. 

The logic of the DAX is as follows, 

• Calculate the working hours of the Selected Month in the Slicer
• Groupby all the records of "one employee" together and calculates its Utilization Efficiency for that month (For this, we first calculate utilization efficiency for each of the employee record for that specific month and then SUM up all the utilization efficiency values)
• At the end, we sum up all the Utilization Efficiencies of all the employees, to get the aggregate value of the complete dataset. 

Now, I need to do one more thing. I need to display the Aggregate Working Hours in a Trend Line or Bar Graph with Months in X-Axis (for this visual, interaction will be turned off with the Months Slicer) 

so, what I did is simply used a Trend Line and placed Month on the X-axis and the Measure on the Y-Axis, but it didn't display any result and showed me the following error, 

Calculation error in measure 'All Measures'[Aggregate_Working_Hours]: Function 'DIVIDE' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

I can't figure out what is the problem. On trying to search the internet, I found that I need to use SUMMARIZE function. I tried to implement the same logic with SUMMARIZE but with SUMMARIZE, I unfortunately can't use CURRENTGROUP and hence, I am not able to replicate the same logic with that. Can anyone help me out in this? 

Here is the POWER BI File if you would like to download: https://drive.google.com/file/d/16SjSNce8Y-nDBQxoFZLGV9GUs-47sc46/view?usp=sharing

[MFelix · Accepted solution]

Hi @HassanAshas ,

Try the following code:

Aggregate_Working_Hours = 

VAR __Working_Hours = NETWORKDAYS(MAX(Competency[Date]), EOMONTH(MAX(Competency[Date]), 0)) * 8

VAR __Table = 
        SUMMARIZE(
            'Competency',
            Competency[Emp Code],
            "__Allocation",SUMX( Competency,DIVIDE((Competency[Allocation]), __Working_Hours, 0))
        )
    VAR __Result = SUMX(__Table,[__Allocation])
RETURN
    __Result

[HassanAshas]

Oh God! How did I miss that 😐 Sorry for such a bad question. Thanks a lot for pointing out the mistake, greatly appreciate it!