Need Sum & Avg in different conditions

[Jraj2380]

Find the below data and i need to Sum the Value when (i)Emp_Name same, (ii) Start/End Dates are Same and (iii) Project Codes are different. And then, Aeverage the value for the given month. Please help me to write a Dax for this output. Thank you.

Emp_Name	PROJECT CODE	Start Date	End Date	ALLOCATION %	UTILIZATION
Ramesh	ABCD	01/10/2024	31/10/2024	100%	Yes
Suresh	EFGH	01/10/2024	31/10/2024	100%	No
Kiran	IJKL	01/10/2024	14/10/2024	100%	Yes
Kiran	MNOP	15/10/2024	31/10/2024	50%	No
Kiran	MNOP	15/10/2024	31/10/2024	50%	Yes

Need below outputs:

Avg Allocation	100%
Avg Utilization - Yes	83%
Avg Utilization - No	75%

[Anonymous · Accepted solution]

Hi @Jraj2380 

Please try the following measure:

Avg Allocation = 
VAR _name = SELECTEDVALUE('Table'[Emp_Name])
VAR _startdate = SELECTEDVALUE('Table'[Start Date])
VAR _enddate = SELECTEDVALUE('Table'[End Date])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Emp_Name] = _name && 'Table'[Start Date] = _startdate && 'Table'[End Date] = _enddate),'Table'[ALLOCATION %])

Avg Utilization - Yes = 
VAR _name = SELECTEDVALUE('Table'[Emp_Name])
VAR _startdate = SELECTEDVALUE('Table'[Start Date])
VAR _enddate = SELECTEDVALUE('Table'[End Date])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[UTILIZATION] = "Yes" && 'Table'[Emp_Name] = _name && 'Table'[Start Date] = _startdate && 'Table'[End Date] = _enddate),'Table'[ALLOCATION %])

Avg Utilization - No = 
VAR _name = SELECTEDVALUE('Table'[Emp_Name])
VAR _startdate = SELECTEDVALUE('Table'[Start Date])
VAR _enddate = SELECTEDVALUE('Table'[End Date])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[UTILIZATION] = "No" && 'Table'[Emp_Name] = _name && 'Table'[Start Date] = _startdate && 'Table'[End Date] = _enddate),'Table'[ALLOCATION %])

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@Jraj2380 

Create measures:

Total Allocation =
SUMX(
FILTER(
'Table',
EARLIER('Table'[Emp_Name]) = 'Table'[Emp_Name] &&
EARLIER('Table'[Start Date]) = 'Table'[Start Date] &&
EARLIER('Table'[End Date]) = 'Table'[End Date] &&
EARLIER('Table'[PROJECT CODE]) <> 'Table'[PROJECT CODE]
),
'Table'[ALLOCATION %]
)

Avg Allocation =
AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Emp_Name],
'Table'[Start Date],
'Table'[End Date],
"TotalAllocation", [Total Allocation]
),
[TotalAllocation]
)

Avg Utilization Yes =
AVERAGEX(
FILTER(
'Table',
'Table'[UTILIZATION] = "Yes"
),
'Table'[ALLOCATION %]
)

Avg Utilization No =
AVERAGEX(
FILTER(
'Table',
'Table'[UTILIZATION] = "No"
),
'Table'[ALLOCATION %]
)

The outputs will be displayed in a card visual or table, depending on your preference:

Avg Allocation: From [Avg Allocation]
Avg Utilization - Yes: From [Avg Utilization Yes]
Avg Utilization - No: From [Avg Utilization No]

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Poojara_D12]

Hi @Jraj2380 

To calculate the required outputs, follow these steps to write a DAX measure. We'll break down the logic step-by-step.

---

1. Group and Sum Allocation

We first need to group the data based on these conditions:

• Emp_Name is the same.
• Start Date and End Date are the same.
• Project Code is different.

For this, use SUMMARIZE to create a temporary table that aggregates the data based on your criteria.

---

DAX Measures

Step 1: Grouped Allocation

Create a measure to calculate the total allocation based on your grouping logic.

Grouped Allocation = 
SUMX(
    SUMMARIZE(
        TableName,
        TableName[Emp_Name],
        TableName[Start Date],
        TableName[End Date],
        TableName[UTILIZATION], // Include UTILIZATION for filtering later
        "Sum Allocation", SUMX(
            DISTINCT(TableName[PROJECT CODE]),
            VALUE(LEFT(TableName[ALLOCATION %], LEN(TableName[ALLOCATION %]) - 1))
        )
    ),
    [Sum Allocation]
)

Step 2: Average Allocation

Calculate the average allocation for the given month.

Avg Allocation = 
DIVIDE(
    [Grouped Allocation], 
    DISTINCTCOUNT(TableName[Emp_Name])
)

Step 3: Average Utilization for "Yes"

Filter the grouped data for utilization marked as "Yes" and calculate the average.

Avg Utilization - Yes = 
DIVIDE(
    SUMX(
        FILTER(
            SUMMARIZE(
                TableName,
                TableName[Emp_Name],
                TableName[Start Date],
                TableName[End Date],
                TableName[UTILIZATION],
                "Sum Allocation", SUMX(
                    DISTINCT(TableName[PROJECT CODE]),
                    VALUE(LEFT(TableName[ALLOCATION %], LEN(TableName[ALLOCATION %]) - 1))
                )
            ),
            TableName[UTILIZATION] = "Yes"
        ),
        [Sum Allocation]
    ),
    DISTINCTCOUNT(
        FILTER(TableName, TableName[UTILIZATION] = "Yes")[Emp_Name]
    )
)

To calculate the required outputs, follow these steps to write a DAX measure. We'll break down the logic step-by-step.

---

1. Group and Sum Allocation

We first need to group the data based on these conditions:

• Emp_Name is the same.
• Start Date and End Date are the same.
• Project Code is different.

For this, use SUMMARIZE to create a temporary table that aggregates the data based on your criteria.

---

DAX Measures

Step 1: Grouped Allocation

Create a measure to calculate the total allocation based on your grouping logic.

DAX

Copy code

Grouped Allocation = SUMX( SUMMARIZE( TableName, TableName[Emp_Name], TableName[Start Date], TableName[End Date], TableName[UTILIZATION], // Include UTILIZATION for filtering later "Sum Allocation", SUMX( DISTINCT(TableName[PROJECT CODE]), VALUE(LEFT(TableName[ALLOCATION %], LEN(TableName[ALLOCATION %]) - 1)) ) ), [Sum Allocation] )

---

Step 2: Average Allocation

Calculate the average allocation for the given month.

DAX

Copy code

Avg Allocation = DIVIDE( [Grouped Allocation], DISTINCTCOUNT(TableName[Emp_Name]) )

---

Step 3: Average Utilization for "Yes"

Filter the grouped data for utilization marked as "Yes" and calculate the average.

DAX

Copy code

Avg Utilization - Yes = DIVIDE( SUMX( FILTER( SUMMARIZE( TableName, TableName[Emp_Name], TableName[Start Date], TableName[End Date], TableName[UTILIZATION], "Sum Allocation", SUMX( DISTINCT(TableName[PROJECT CODE]), VALUE(LEFT(TableName[ALLOCATION %], LEN(TableName[ALLOCATION %]) - 1)) ) ), TableName[UTILIZATION] = "Yes" ), [Sum Allocation] ), DISTINCTCOUNT( FILTER(TableName, TableName[UTILIZATION] = "Yes")[Emp_Name] ) )

---

Step 4: Average Utilization for "No"

Similarly, calculate the average for utilization marked as "No".

Avg Utilization - No = 
DIVIDE(
    SUMX(
        FILTER(
            SUMMARIZE(
                TableName,
                TableName[Emp_Name],
                TableName[Start Date],
                TableName[End Date],
                TableName[UTILIZATION],
                "Sum Allocation", SUMX(
                    DISTINCT(TableName[PROJECT CODE]),
                    VALUE(LEFT(TableName[ALLOCATION %], LEN(TableName[ALLOCATION %]) - 1))
                )
            ),
            TableName[UTILIZATION] = "No"
        ),
        [Sum Allocation]
    ),
    DISTINCTCOUNT(
        FILTER(TableName, TableName[UTILIZATION] = "No")[Emp_Name]
    )
)

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS