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. 

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI MPSG 

Try

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

MPSG's avatar
MPSG
New Member
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