Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Average and Count in same Graph

I am not able to include sample files so I hope I can explain this correctly.  I have a table with a column that the responses can range amoung several different answers.  What I am trying to do is some of them I want to lump under the same answer but still graph off of this number.  I tried putting them together under the same response using an IF but it is adding them all together.  I am looking for an average for them.

Example

Response     Count

1b                 2

1c                 1

1e                5

1d                 4

2

3

4

5

6

7

1a                 3

 

I want all the 1's to be listed as 1 and instead of giving me back a total of 15 I want the average of all of them but still be able to give the counts for the remainder 2 - 7.  I hope I am explaining this well enough.  Thanks for any help you can give.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    I think you can enter to query editor to add a column to check type and filter your formula to calculate average of rows which 'isnumber' is 'true'.

     

    Sample:

     

    Query:

    = Table.AddColumn(#"Changed Type", "Isnumber", each try Number.FromText([Test]) is number otherwise false)

     

    Result:

     

    Regards,

    Xiaoxin Sheng

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    You can try to use below measure to modify the total row to show average count:

    AVG Count =
IF (
    COUNTROWS ( 'Sample' ) = COUNTROWS ( ALLSELECTED ( 'Sample' ) ),
    AVERAGEX (
        SUMMARIZE (
            ALLSELECTED ( 'Sample' ),
            [Response],
            "Count", COUNT ( 'Sample'[Response] )
        ),
        [Count]
    ),
    COUNT ( 'Sample'[Response] )
)

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I have one problem, the field I am trying to avg and count has some numbers and string values in it.  I am only looking to average and count the number ones.  I tried filtering the visual but I am pulling this directquery and telling me average is not used.  I turned on the allwo restricted but it is still having the trouble with the string values.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous,

         

        I'm no very clear for your description, can you please share the sample pbix file for test?

         

        >> I am only looking to average and count the number ones. 

        If you want to deal with numeric part, I'd like to suggest you enter to query editor and add a custom column to store splitted numeric part to calculate.

        PowerQuery | Extract Numbers from A string (eg ABCD1234)

         

        Regards,

        Xiaoxin Sheng