Forum Discussion
Dividing the Subtotal without changing Values in Matrix
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 |
Hi,
PBI file attached.
Hope this helps.
10 Replies
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])))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.
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:
- Ashish_Mathur
Super User
Hi,
Share the download link of the PBI file.
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
