Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Average per User

Hi,   I have 10 participants in a survey and I want to calculate the average per user. This number I want to be a whole number so I can count the distribution of the participants from 1-5.   For...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    I think Avg and GenCal are all measures. I think you can try Round function to get whole number. 

    My Sample:

    AvgGen = AVERAGE('Table'[Gen])
    GenCalc = ROUND([AvgGen],0)

    Result is as below.

    Then create a table from 1 to 5 and we need to add this column into X axis in column chart.

    X axis = 
GENERATESERIES(1,5,1)

    Try my measure to count GenCalc.

    Measure = 
COUNTAX(FILTER(VALUES('Table'[User]),[GenCalc]=MAX('X axis'[Value])),[User])

    Result is as below.

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , This means you need segmentation ,

 

Create a table

bucket = generateseries(1,5,1)

 

Then create a measure

 

var _tab = addcolumns(summarize('Table','Table'[participants ID],), "GenCalb",[GenCal])
return
countx(filter(_tab, [GenCalb] = max(bucket[Value])),[_1])

 

refer for more details

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    It will not make me make a return:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

      I think Avg and GenCal are all measures. I think you can try Round function to get whole number. 

      My Sample:

      AvgGen = AVERAGE('Table'[Gen])
      GenCalc = ROUND([AvgGen],0)

      Result is as below.

      Then create a table from 1 to 5 and we need to add this column into X axis in column chart.

      X axis = 
GENERATESERIES(1,5,1)

      Try my measure to count GenCalc.

      Measure = 
COUNTAX(FILTER(VALUES('Table'[User]),[GenCalc]=MAX('X axis'[Value])),[User])

      Result is as below.

      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.