How to calculate YTD Average based on a selected date.

[Mavericks334]

Hi

I have the below DAX measure,

=calculate([Value_Corrected1],datesbetween(Dates[Date],Year_Period[Selected_Year_Start_Date],Year_Period[Selected_Month_End_Date]))

That is giving me the sum of headcount for the dates that i select. However i need to get the average of the headcount for the period selected.

Any idea how it oculd be calculated.

Regards,
Renato.

 

[powerpivotpro]

Forgive me if I have misunderstood, but if your original measure [Value_Corrected1] had been defined as an AVERAGE() measure rather than a SUM(), this would give you what you wanted.

 

[Mavericks334]

The problem, is each dept has 3 headcount 4 , 40 ,25 it varies, when i do an average it gives me 6.4 , 7 where i need to get 1020 because of the total hc.

 

[powerpivotpro]

Still don't precisely understand your question yet, but I have a hunch you might need to use the AVERAGEX() function. Something like AVERAGEX(VALUES(Table[Dept]), Table[HeadCount]) as your base measure, then use that base measure in your CALCULATE() with DATESBETWEEN().

SUMX is covered here - AVERAGEX works the same way:

SUMX() – The 5-point palm, exploding fxn technique « PowerPivotPro

 

[Mavericks334]

My Raw data table consist of Dept Id's and the hc associated with them, the dept table consists the dept id's and Functional unit aligned to It. What i am trying to acheive, is based on the functional unit to get an average head count. Each dept could have any number of people associated with it and functional headcount is the sum of each dept associated. When i try to get the average for two month or 3 months based on my date select, what it does is, sums the entire rows for that period and divides it by the count of rows, which gives me say 6.5, 7, instead of suming up the total rows and then taking an average.

 

[powerpivotpro]

Is there a relationship between those two tables?

 

[Mavericks334]

Yes there is a relationship, using the dept id's

 

[Mavericks334]

Rob, Below is my Sample Data[TABLE="width: 162"]
<COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY>[TR]
[TD="class: xl69, width: 68, bgcolor: #4f81bd"]Date[/TD]
[TD="class: xl70, width: 43, bgcolor: #4f81bd"]Value[/TD]
[TD="class: xl70, width: 105, bgcolor: #4f81bd"]Heading2_Code[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]6[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310ED[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl68, bgcolor: transparent"]3310FL[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]33[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310HD[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl68, bgcolor: transparent"]3310PZ[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]11[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310RC[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"]3310SG[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]10/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]7[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310SH[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]11/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]6[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310ED[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]11/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl68, bgcolor: transparent"]3310FL[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]11/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]33[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310HD[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]11/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl68, bgcolor: transparent"]3310PZ[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]11/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]11[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310RC[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]11/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"]3310SG[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]11/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]7[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310SH[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]12/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]6[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310ED[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl68, bgcolor: transparent"]3310FL[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]12/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]33[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310HD[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl68, bgcolor: transparent"]3310PZ[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]12/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]11[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310RC[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12/1/2011[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"]3310SG[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1, align: right"]12/1/2011[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]7[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]3310SH[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 136"]
<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY>[TR]
[TD="width: 92"]Row Labels[/TD]
[TD="width: 89"]Sum of Value[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]10/1/2011[/TD]
[TD="bgcolor: transparent, align: right"]98[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]11/1/2011[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]12/1/2011[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65"]Grand Total[/TD]
[TD="align: right"]297[/TD]
[/TR]
</TBODY>[/TABLE]


If i select my data as nov then it must give me the average of (98,99) which 98.5. and similar for other months. That is why i am using sum 1st and then trying and average. to get the values. If i try a direct average it gives me on 14.14 for the above data, which is not the average headcount for all the 3 months.

Do let me know incase you need additional clarifications.

 

[powerpivotpro]

Can you send me the workbook, or a workbook with sample data illustrating the problem? Email address in my signature.

 

[Mavericks334]

Hi Rob,

I have sent you the details as requested.

Please check it out and let me know incase you need any more clarifications.

Regards,
Renato.