Caculating Average for Sum of a Column with Slicers

[aharris15]

Hello,

I'm trying to calculate the average per Person per Office ID of Direct Labor Time. The formulas I've attempted so far have not yielded the proper results. The idea is to have this measure in a single card and needs to be reactive to the date and Office ID slicers on the page. 

Below is a sample of my data. The results that I'd expect is for OH to have an average of 12.5 hours for Direct Labor Time and PA to have an average of 10.25 hours. 

Any help is appreciated!

Name	Office ID	Labor Time	Labor Type
Adam	PA	2.5	Direct
Adam	PA	1	Direct
Adam	PA	1.25	Indirect
Adam	PA	4.5	Direct
Adam	PA	1.5	Indirect
Adam	PA	1	Direct
Eric	OH	0.5	Indirect
Eric	OH	0.5	Indirect
Eric	OH	5	Direct
Eric	OH	1.25	Direct
Eric	OH	2.5	Direct
Eric	OH	1.5	Direct
Eric	OH	1	Indirect
Eric	OH	5.75	Direct
Eric	OH	4	Indirect
Eric	OH	2.25	Indirect
Ivan	PA	1	Direct
Ivan	PA	6	Indirect
Ivan	PA	6.5	Direct
Ivan	PA	1	Direct
Ivan	PA	5.75	Indirect
Ivan	PA	3	Direct
Ivan	PA	1	Indirect
Ivan	PA	1	Indirect
Owen	OH	3.75	Direct
Owen	OH	1	Indirect
Owen	OH	6	Indirect
Owen	OH	0.5	Direct
Owen	OH	0.75	Direct
Owen	OH	1.5	Direct
Owen	OH	0.5	Direct
Owen	OH	1	Indirect
Owen	OH	2	Indirect
Jeremy	OH	3	Indirect
Jeremy	OH	6	Direct
Jeremy	OH	2.5	Direct
Jeremy	OH	0.5	Direct
Jeremy	OH	4	Direct
Jeremy	OH	1.5	Direct
Jeremy	OH	5.5	Indirect
Jeremy	OH	0.75	Indirect

[v-jayw-msft · Accepted solution]

Hi @aharris15 ,

Check the document about var.

https://docs.microsoft.com/en-us/dax/var-dax 

And check the measures below.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER(ALLEXCEPT('Table','Table'[Office ID]),'Table'[Labor Type]="Direct"))

Measure 2 = CALCULATE(SUM('Table'[Labor Time]),FILTER('Table','Table'[Office ID]=SELECTEDVALUE('Table'[Office ID])&&'Table'[Labor Type]="Direct"))

Measure 3 = [Measure 2]/[Measure]

Result would be shown as below.

Best Regards,

Jay

[aharris15]

Help is still needed!!

[aharris15]

Still looking for help on this if anyone has any ideas!

[aharris15]

Hi @Fowmy 

Can you explain your measure a little more. For example, I don't see VAR_T as a function. Also, do you need to fill in anything for the RETURN portion?

Sorry - I'm still learning with DAX.

[Fowmy]

@aharris15 

Add the following measure:

Avg per Person per Office ID Direct = 
VAR __T = 
CALCULATETABLE(
    SUMMARIZE(Table7 , Table7[Name], Table7[Office ID] , "__Avg", SUM(Table7[Labor Time])),
    Table7[Labor Type] = "Direct"
)
RETURN
AVERAGEX( __T, [__Avg])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn