Forum Discussion

MPSG's avatar
MPSG
New Member
5 years ago
Solved

Dynamic Countrow within Sumx

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

Need Help
  • MPSG's avatar
    MPSG
    5 years ago

    Hi Anonymous 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
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI MPSG 

    Try

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

    • MPSG's avatar
      MPSG
      New Member
      5 years ago

      Hi Anonymous 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
  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.