Forum Discussion

MPSG's avatar
MPSG
New Member
6 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              ...
Need Help
  • MPSG's avatar
    MPSG
    6 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
    6 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. 

     

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 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!