topic Re: Average of Dates over Year in DAX Commands and Tips

Average of Dates over Year

sgv2000 — Mon, 17 Aug 2020 13:36:10 GMT

I have been searching for a way to do this for a couple days. Hopefully someone has a simple solution that I am missing.
Basically what I want to do is average weekly or monthly totals by year. So, I have detailed data and I put it in a grid and can get the sum by week easy enough. If I try to average that, I get the average of the detail values, not the average of the total. I figured out how to get around that and get the average of the totals at the bottom of the grid. Now what I want to do is get that average split by year and repeated on every row of the grid so I can put it on a chart. Here is a picture that might explain it better. Any thoughts would be greatly appreciated.

Re: Average of Dates over Year

Greg_Deckler — Mon, 17 Aug 2020 13:43:01 GMT

sgv2000 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Re: Average of Dates over Year

sgv2000 — Mon, 17 Aug 2020 13:50:33 GMT

Greg_Deckler This is an interesting article. I find it a little difficult to follow. From what I can tell, it explain how to do what I figure out. It shows how to use the group or summarize function to group by date or month then average that to get a total at the bottom of the grid or by a specific category, like customer. I need to be able to use something like ALLEXCEPT to group the group by year and get the average for the year and the display that back on every date for the year. This will make a flat line for the year and show how the average is changing one year over the next.

Re: Average of Dates over Year

Greg_Deckler — Mon, 17 Aug 2020 14:01:42 GMT

sgv2000 - So like

AVERAGEX(ALLEXCEPT('Table'[Year]),'Table'[Value])

If not, would potentially help to post sample data as text and expected outcome.

https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Average of Dates over Year

sgv2000 — Mon, 17 Aug 2020 14:24:18 GMT

Greg_Deckler

Here is a table of data. I want this to end up looking like the table and chart in the original post. I need to group by Month and have the total for the month. Easy enough, just add that to a grid, done. Next I need a column that is the average os the monthly totals for the year repeated on every month in the grid. Just like the original picture I posted.

Year	Month	Day	Value
2019	1/1/2019	1/1/2019	25
2019	1/1/2019	1/2/2019	50
2019	1/1/2019	1/3/2019	25
2019	2/1/2019	2/1/2019	20
2019	2/1/2019	2/2/2019	10
2019	2/1/2019	2/3/2019	20
2019	3/1/2019	3/1/2019	25
2019	3/1/2019	3/2/2019	15
2019	3/1/2019	3/3/2019	35
2019	4/1/2019	4/1/2019	5
2019	4/1/2019	4/2/2019	10
2019	4/1/2019	4/3/2019	10
2019	5/1/2019	5/1/2019	100
2019	5/1/2019	5/2/2019	100
2019	5/1/2019	5/3/2019	100
2019	6/1/2019	6/1/2019	50
2019	6/1/2019	6/2/2019	20
2019	6/1/2019	6/3/2019	20
2019	7/1/2019	7/1/2019	25
2019	7/1/2019	7/2/2019	15
2019	7/1/2019	7/3/2019	35
2019	8/1/2019	8/1/2019	20
2019	8/1/2019	8/2/2019	10
2019	8/1/2019	8/3/2019	20
2019	9/1/2019	9/1/2019	50
2019	9/1/2019	9/2/2019	125
2019	9/1/2019	9/3/2019	75
2019	10/1/2019	10/1/2019	25
2019	10/1/2019	10/2/2019	50
2019	10/1/2019	10/3/2019	25
2019	11/1/2019	11/1/2019	100
2019	11/1/2019	11/2/2019	100
2019	11/1/2019	11/3/2019	100
2019	12/1/2019	12/1/2019	55
2019	12/1/2019	12/2/2019	20
2019	12/1/2019	12/3/2019	20
2020	1/1/2020	1/1/2020	25
2020	1/1/2020	1/2/2020	15
2020	1/1/2020	1/3/2020	35
2020	2/1/2020	2/1/2020	50
2020	3/1/2020	3/1/2020	450
2020	4/1/2020	4/1/2020	200
2020	5/1/2020	5/1/2020	100
2020	6/1/2020	6/1/2020	325
2020	7/1/2020	7/1/2020	75
2020	8/1/2020	8/1/2020	150
2020	9/1/2020	9/1/2020	125
2020	10/1/2020	10/1/2020	95
2020	11/1/2020	11/1/2020	120
2020	12/1/2020	12/1/2020	300

Re: Average of Dates over Year

Anonymous — Mon, 17 Aug 2020 16:45:09 GMT

HI sgv2000 ,

Create these measures

VAl = SUMX( SUMMARIZE('Table','Table'[Month],"Ave",SUM('Table'[Value])),[Ave])

AVR of Year = SUMX(FILTER(ALL('Table'),'Table'[Year] = MAX('Table'[Year])),[VAl]/12)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Average of Dates over Year

sgv2000 — Mon, 17 Aug 2020 17:41:43 GMT

Anonymous this looks close. The 12 can't be hard coded though. It is variable. I am playing with this to see if I can make it work.

Thanks,

Re: Average of Dates over Year

Anonymous — Mon, 17 Aug 2020 19:36:28 GMT

HI sgv2000 ,

12 here is the number of months in a year.

Incase you do not want to hardcode

AVR of Year = var a= CALCULATE(DISTINCTCOUNT('Table'[Month]),FILTER(ALL('Table'),'Table'[Year] = MAX('Table'[Year]))) RETURN SUMX(FILTER(ALL('Table'),'Table'[Year] = MAX('Table'[Year])),[VAl]/a)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Average of Dates over Year

sgv2000 — Tue, 18 Aug 2020 20:36:46 GMT

Anonymous, Thanks again for your reply. I have been working today to implement this in my report. I finally figured out why I am having an issue now. Your use of the ALL key work basically ignores any slicers on the report. See, I showed you data without all of the many dimensions in the report. You solution will work if you have no other slicers on the report. Once you introduce a slicer, this becomes a problem. Example would be to add another column to the data and duplicate the data. Call it fruit and lable half apples and half bananas. Increase all the numbers in bananas by *10 just to verify. Setup a slicer on fruit and see if it will work.

Re: Average of Dates over Year

sgv2000 — Tue, 18 Aug 2020 20:43:57 GMT

Anonymous , I was able to make your solution work by changing ALL to ALLSELECTED. Thanks for your help. Just wish I had caught that ealier.

Re: Average of Dates over Year

Anonymous — Wed, 19 Aug 2020 04:56:38 GMT

Hi sgv2000 ,

Great that the solution helped. 🙂

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)