Dynamic Countrow within Sumx

[MPSG]

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

[MPSG · Accepted 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:

divide(sumx(SUMMARIZE(table,capacity,unit),capacity]), countrows(SUMMARIZE(table,capacity,unit)))

Best regards

[v-rzhou-msft · Accepted solution]

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. 

[AllisonKennedy]

@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!

[pranit828]

HI @MPSG 

Try

SUMX(CALCULATE(AVERAGE(table[capacity]),ALLEXCEPT(table,table[unit]))

[MPSG · Accepted 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:

divide(sumx(SUMMARIZE(table,capacity,unit),capacity]), countrows(SUMMARIZE(table,capacity,unit)))

Best regards