Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I think this is probably a very simple question for the experts but I'm a complete newbie to DAX (and this forum) and would really appreciate your help on this.
A mockup of my data looks like this
Project | Date | Person | Volume |
1 | Feb-21 | Bill | 1.24 |
2 | Feb-21 | Bill | 0.48 |
3 | Mar-21 | Bill | 0.06 |
1 | Jul-21 | Bill | 0.87 |
2 | Jul-21 | Bill | 0.24 |
1 | Jan-21 | Sally | 0.23 |
2 | May-21 | Sally | 0.11 |
2 | Jun-21 | Sally | 0.45 |
3 | Jun-21 | Sally | 0.12 |
1 | Jun-21 | Fred | 0.06 |
4 | Jun-21 | Fred | 0.77 |
The monthly sums by person for these data are
Row Labels | Jan | Feb | Mar | May | Jun | Jul |
Bill | 1.72 | 0.06 | 1.11 | |||
Sally | 0.23 | 0.11 | 0.57 | |||
Fred | 0.83 |
I'd like to be able to calculate the average of these monthly sums. It should be 0.66. I've tried various permutations of AVERAGE function but have not been able to solve this. I appreciate it's a basic sort of question and that I have a LOT to learn!
Thanks in advance!
Solved! Go to Solution.
Hi @IRJ ,
Create a new measure and put it in the visual.
The average of monthly totals =
VAR _total_volume=SUM('Table'[Volume])
VAR _months =DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),MONTH)+1
RETURN
_total_volume/_months
The result should be like this.
Attach the pbix file for reference. Hope it helps.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
very helpful replies. thank you
Hi @IRJ ,
Create a new measure and put it in the visual.
The average of monthly totals =
VAR _total_volume=SUM('Table'[Volume])
VAR _months =DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),MONTH)+1
RETURN
_total_volume/_months
The result should be like this.
Attach the pbix file for reference. Hope it helps.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
@IRJ , You need to try a measure like
AverageX(summarize(Table, Table[Name], Table[Month], "_1", sum(Table[VOlume])), [_1])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |