topic Re: Need Sum & Avg in different conditions in DAX Commands and Tips

Need Sum & Avg in different conditions

Jraj2380 — Mon, 18 Nov 2024 10:39:23 GMT

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%

Re: Need Sum & Avg in different conditions

Poojara_D12 — Mon, 18 Nov 2024 11:09:54 GMT

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

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

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

Re: Need Sum & Avg in different conditions

Kedar_Pande — Mon, 18 Nov 2024 12:50:45 GMT

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

Re: Need Sum & Avg in different conditions

Anonymous — Tue, 19 Nov 2024 08:06:42 GMT

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.