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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I see how this is very useful, but my problem us the field could have an answer of a number in one row and another string, not together in the same field.

         

        Yes

        0

        1

        14:35

        Nothing seen here

        1

        1

        0

        1/1/15

         

        That is the problem.  I am just trying to average the numbers for a specific entry.