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,
My skills in PowerBI are quite limited so I think this should be an easy question for you 🙂
I have the following table for example:
Unit Capacity Month
1 10 5
1 10 6
1 10 7
2 11 5
2 11 7
3 30 7
I am trying to create a measure that gives me the average value for the capacity.
However I don't just want a simple average of sum(capacity)/countrows.
My aim is to just have capacity for unit1 + capacity for unit2 + capacity for unit 3 / number of units
This would be (10 + 11 + 30) /3 = 17
I thought with a sumx it could work:
=Divide(Sumx(table, Capacity/Calculate(Countrow(table), FILTER)),Distinctcount(Unit))
The part I am struggling with is the FILTER as I need countrow to be a dynamic value instead of static so that I could have the following sum:
(10/3+10/3+10/3+11/2+11/2+30/1)/3 = 17
So the countrow needs to be either 3 or 2 or 1
Any ideas?
Best regards
Solved! Go to Solution.
Hi @pranit828 thanks for the help.
I couldn't make it work with that solution though.
I think I was able to solve it now with the SUMMARIZE:
Hi @MPSG
Summariz function is a good way to solve this problem, and you may try my way use addcolumn and average function.
Measure =
VAR _Result = DIVIDE(SUMX(ADDCOLUMNS(GENERATESERIES(1,3,1),"Avg",AVERAGEX(FILTER('Table','Table'[Unit]=[Value]),'Table'[Capacity])),[Avg]),DISTINCTCOUNT('Table'[Unit]))
return
_Result
Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MPSG
Summariz function is a good way to solve this problem, and you may try my way use addcolumn and average function.
Measure =
VAR _Result = DIVIDE(SUMX(ADDCOLUMNS(GENERATESERIES(1,3,1),"Avg",AVERAGEX(FILTER('Table','Table'[Unit]=[Value]),'Table'[Capacity])),[Avg]),DISTINCTCOUNT('Table'[Unit]))
return
_Result
Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@MPSG
If you want Average capacity, use AVERAGEX()
Whatever value you want in the denominator for the AVERAGE should match the number of rows in the table you give to the AVERAGEX function.
See this post that might explain why and how this works
@MPSG forgot to post the link, here it is: https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html
(I'd love feedback on it so feel free to leave a comment, as averages are SUPER confusing and it took me a while to wrap my head around them, so not sure I've done a great job communicating my knowledge, but I'm trying).
I think you're looking for:
AVERAGEX(VALUES(table[Unit]), AVERAGE(Table[Capacity]))
In your sample data, Capacity is always the same for a selected Unit, so you could use MAX(Table[Capacity]) instead of AVERAGE(Table[Capacity]), or MIN, but just depends what you actually need to measure.
Hope that helps!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
HI @MPSG
Try
SUMX(CALCULATE(AVERAGE(table[capacity]),ALLEXCEPT(table,table[unit]))
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @pranit828 thanks for the help.
I couldn't make it work with that solution though.
I think I was able to solve it now with the SUMMARIZE:
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 |
---|---|
106 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |