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 instance: Below all 10 participants are 3 in the scale of 1-5. How can I count the participants being 3 so I can show that there's 10 participants in '3'?

 

I have made this calculation:

 

Edit:
I might be uncelar but I want a bar like below:

 

I hope you can help 🙂

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.

3 Replies

  • 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.