Average and Count in same Graph

[Anonymous]

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.

[v-shex-msft]

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]

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.

[v-shex-msft]

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]

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.

[v-shex-msft · Accepted solution]

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]

I did try this and it was working for on of my dashbaords, but the one I am trying to use it on is a DirectQuery and when I went into the Advanced Query Editor, it told me I could not do this adding a new query.  Am I placing this in the wrong spot on the file.  Please advise.  Thanks

[v-shex-msft]

HI @Anonymous,

It seems like direct query not support to add custom columns. I haven't found other ways to work through this issue.

Regards,

Xiaoxin Sheng