topic Re: Average across a time period, but sum rows in DAX Commands and Tips

Average across a time period, but sum rows

jsangerman — Fri, 17 Dec 2021 21:06:05 GMT

I have data for percentage of time people spend on a project. It looks like this:

Team Member	Project	October 2021	November 2021	December 2021	January 2022	February 2022	March 2022	April 2022
Adam	Project Alpha	40%	40%	40%	40%	40%	40%	40%
Adam	Project Beta	40%	40%	40%	40%	40%	40%	40%
John	Project Alpha		25%	25%	25%
John	Project Beta	100%	100%	100%	100%	100%	100%	100%
Alan	Project Beta	100%	100%	100%	100%	100%	100%	100%

What I want is to be able to sum the rows, but average across columns as I drill up in dates. So, if I set my rows as Team Member in a table, I should see this:

Team Member	October 2021	November 2021	December 2021	January 2022	February 2022	March 2022	April 2022
Adam	80%	80%	80%	80%	80%	80%	80%
John	100%	125%	125%	125%	100%	100%	100%
Alan	100%	100%	100%	100%	100%	100%	100%

That's easy. However, if I drill up on or summarize the columns to Quarters or Years, if should look like this:

Team Member	Q4 2021	Q1 2022	Q2 2022
Adam	80%	80%	80%
John	116.6%	108.3%	100%
Alan	100%	100%	100%

Team Member	2021	2022
Adam	80%	80%
John	116.6%	106.25%
Alan	100%	100%

How can I work my formula to do that?

Re: Average across a time period, but sum rows

AlexisOlson — Fri, 17 Dec 2021 21:19:25 GMT

Try averaging at the year-month level of granularity.

MonthlySumAvg = AVERAGEX ( SUMMARIZE ( dimDate, dimDate[Year], dimDate[Month] ), CALCULATE ( SUM ( dataTable[Complete%] ) ) )

Re: Average across a time period, but sum rows

Anonymous — Tue, 21 Dec 2021 01:23:29 GMT

Hi jsangerman ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

Re: Average across a time period, but sum rows

jsangerman — Wed, 22 Dec 2021 16:30:26 GMT

That seems to do it. Thanks, Alexis.