Dividing the Subtotal without changing Values in Matrix

[LickNewin]

I'm trying to create a Power BI Measure that calculates an Average based on the amount of years (We use a relative date for the past 5 years, so the average should just be the sum of the row, divided by 5). I've tried to write the below Measure which adds the Average as a column but it's not calculating it correctly.

This is what it should look like - notice that the Average should be the total of each Age Bracket, divided by 5 for the amount of years.

As an example, for the '18-25' Age Bracket, the Sum total is 11, and when divided by 5, should give an average of 2.20 over 5 years.

Age Bracket	2020	2021	2022	2023	2024	Average
Junior	0	0	1	0	0	0.20
18-25	2	2	3	4	0	2.20
26-30	2	2	3	0	0	1.40
31-35	0	0	2	3	0	1.00
36-40	0	0	1	2	0	0.60
41-45	0	1	1	0	0	0.40
46-50	0	1	2	5	0	1.60
51-55	0	1	2	0	0	0.60
56-60	1	1	3	1	0	1.20
61-65	0	3	4	0	0	1.40
66-70	1	6	2	5	0	2.80
71-75	2	5	4	4	0	3.00
76-80	2	5	11	5	0	4.60
81-85	0	6	5	11	0	4.40
86-90	2	3	6	6	0	3.40
91-95	0	0	1	2	0	0.60
96-99	0	1	0	0	0	0.20
100+	1	0	0	0	0	0.20

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[LickNewin]

Working Average Function.pbix

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[LickNewin]

Hey Ashish,

Thanks so much! Those values seem to work on your provided file, but I can't seem to replicate it. How did you accomplish this?

I've created the Table named Calendar and added the 'Year' calculation, as well as changing the Measure code, but mine comes out like this:

[Ashish_Mathur]

You are welcome.  Create a relationship (Many to One and Single) from the Resignation date column to the Date column of the Calendar table.

[LickNewin]

You're a legend! I've been stuck on this for 2 days.

That looks to be working now 🙂

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[v-jialongy-msft]

Hi @LickNewin 

Please try the following DAX:

Average Over 5 Years = 
SUMX(
    VALUES('JoinedDate1'[Age Bracket]), 
    CALCULATE(SUM('JoinedDate1'[Member Number])) / 5
)

Best Regards,

Jayleny

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

[LickNewin]

Hey Jayleny, 

Unfortunately this doesn't work as the Column 'Age Bracket' is a String, not a number.

Some of the age brackets are "Junior" or "100+" so include characters other than numbers.

Please see the example PBIX attached here:

Working Average Function.pbix

[LickNewin]

It's a little hard to see, but the current measure that isn't working is:

Measure =

AVERAGEX(VALUES(JoinedData1[Resignation Year]), CALCULATE(COUNT(JoinedData1[Member Number])))