Calculating Average for Column Over Certain Number of Months

[VSepaha]

Hi Everyone,

I am working on creating a table that can show the average number of requested talent for a certain skill. I currently have a table as shown below:

The middle column in the 'Average Requested Number of Talent per Month' over a period of 4 months. As you can see from the table, most of the values are calculated correctly, for exmaple, PYTHON has 30 Talent requested and the average is (30/4) 7.5. However, when we got lower into MS WINDOWS AZURE it has 22 Talent requested and the average comes to 11.  I'm not sure why this is the case.

I created this measure through a quick measure and this is the DAX that it returned:

Avg Requsted Number of Talent per Month =

AVERAGEX(

    KEEPFILTERS(VALUES('Gap Operating Trends - Gap'[REQUEST_CREATED_DATE].[Month])),

    CALCULATE(SUM('Gap Operating Trends - Gap'[REQUESTED_NUMBER_OF_TALENT]))

)

Could someone help me in figuring out why this is giving me correct values in some cases and not others, and how I can go about getting the correct values?

[sanalytics]

@ VSepaha,

It would be great if you provide us some sample data and your desired output..Providing only a screenshot is very difficult to assume your exact requirement..
if i understand correctly,your last two skill name are returning wrong average.So for that 

Avg Requested Number of Talent per Month = DIVIDE(SUM('Table'[Requested Number of Talent]),4)

can give you desire result..apart from that, I am unclear with your requirement.

regards,

sanalytics

[VSepaha]

The calculation you sent me does work correctly, however, I have a filter on the page which specifies the last X number of months. By default the number of months is set to 4. In that case,

"Avg Requested Number of Talent per Month = DIVIDE(SUM('Table'[Requested Number of Talent]),4)"

would work correctly. But if I changed the filter to the past 5 months, this formula would be invalid. Is there a way of how I can get the number of months being used by the filter?

[sanalytics]

@VSepaha,

Then use the Selectedvalue function for that..
Avg Requested Number of Talent per Month = DIVIDE(SUM('Table'[Requested Number of Talent]),

SELECTEDVALUE(ColumnName,Alternateresult))

Regards,

sanalytics

[VSepaha]

I'm still fairly new to Power BI, I'm not sure what goes in the Alternateresult value.

Would something like this work after I figure out the Alternateresult?

Avg Requested Number of Talent per Month = DIVIDE(SUM('Table'[REQUESTED_NUMBER_OF_TALENT]),

SELECTEDVALUE('Table'[Date].[Month], {Alternateresult})

Thanks again for the help!

[V-lianl-msft]

Hi @VSepaha ,

Try:

Avg Requested Number of Talent per Month = DIVIDE(SUM('Table'[REQUESTED_NUMBER_OF_TALENT]),
DISTINCTCOUNT('Table'[Date].[Month])

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

[VSepaha]

Hi @V-lianl-msft 

This solution didn't seem to work for me. After applying this solution, it divided my 'Requested Number of Talent' by 12 instead of the number of months that I am filtering by.

Could you please help me find a solution that takes the filter into account?

Thank you!

[V-lianl-msft · Accepted solution]

Hi @VSepaha ,

Sorry for the late reply.

Create a measure like this:

Measure = CALCULATE(AVERAGE('Table'[value]),ALLSELECTED('Table'))

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