Forum Discussion
Measure Efficiency -- Get distinct values, average per single date, then sum the averages
Hi ryan25r9 ,
Or like this?
Measure =
VAR x =
AVERAGEX(
FILTER(
TLG,
TLG[Date Worked] = SELECTEDVALUE(TLG[Date Worked])
),
TLG[Workday Days]
)
RETURN
ROW(
"Test",
SUMX(
TLG,
x
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry, this doesn't quite get there either. It doesn't account for there being duplicate TLG[Workday Days] per Date Worked & Employee.
Probably helps to give an example. Starting dataset:
Employee ID Date Worked Workday Days TLP ID 491 01/05/20 0 10 491 01/06/20 0.5 4 491 01/06/20 0.5 941 491 01/07/20 1 331 491 01/08/20 1 596 491 01/08/20 1 2537 11402 01/06/20 1 319 11402 01/06/20 1 782 11402 01/07/20 1 331 11402 01/08/20 1 841 99821 01/05/20 0 10 99821 01/06/20 1 319 99821 01/07/20 1 2537 99821 01/07/20 1 596 99821 01/07/20 1 941 2729 01/05/20 1 331 2729 01/05/20 1 1931 2729 01/06/20 0.5 841 Distinct values of Employee ID, Date Worked, & Workday Days:
Employee ID Date Worked Workday Days 491 01/05/20 0 491 01/06/20 0.5 491 01/07/20 1 491 01/08/20 1 11402 01/06/20 1 11402 01/07/20 1 11402 01/08/20 1 99821 01/05/20 0 99821 01/06/20 1 99821 01/07/20 1 2729 01/05/20 1 2729 01/06/20 0.5 AVERAGE Workday Days by day:
Date Worked Average Workday Days 01/05/20 0.333 01/06/20 0.750 01/07/20 1.000 01/08/20 1.000 Lastly, the SUM of averages, making the expected result of the measure here:
3.083 Thanks for providing sample data and output. This measure should work for you in a table with Dates on the rows.
NewMeasure =
VAR __summary =
ADDCOLUMNS (
SUMMARIZE ( TLG, TLG[Employee ID], TLG[Date Worked] ),
"AvgHrs", CALCULATE ( AVERAGE ( TLG[Workday Days] ) )
)
RETURN
AVERAGEX ( __summary, [AvgHrs] )If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Greatly appreciate the reply! Unfortunately this measure is in an SSAS tabular model, so I need it to work in all contexts and can't limit to a table with dates on the rows.
Greatly appreciate the Power BI file, thanks so much. This actually returns the correct results, but running on the entire model drops the efficiency from 25 seconds to 171 seconds. The core table (TLG) is 700M rows, so that certainly isn't helping with the context transition. For reference, this is the old code simplified and your recommended code altered to run in DAX Studio:
EVALUATE VAR Old = SUMX( GROUPBY( SUMMARIZE(TLG, TLG[Date Worked], TLG[Employee ID], TLG[Workday Days]) , [Date Worked] , "AvgWorkdays" , AVERAGEX( CURRENTGROUP(), [Workday Days] ) ) , [AvgWorkdays] ) VAR New = SUMX ( VALUES ( 'TLG'[Date Worked] ), AVERAGEX ( SUMMARIZE ( VALUES ( TLG[Employee ID] ), [Employee ID], "AvgWorkdays", CALCULATE(AVERAGE(TLG[Workday Days])) ), [AvgWorkdays] ) ) RETURN ROW("test", New)
