cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mchristy Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Average and Count in same Graph

Hi @mchristy,

 

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:

11.PNG

 

Query:

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

 

Result:

12.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
7 REPLIES 7
Community Support Team
Community Support Team

Re: Average and Count in same Graph

Hi @mchristy,

 

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] )
)

11.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mchristy Regular Visitor
Regular Visitor

Re: Average and Count in same Graph

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.

Community Support Team
Community Support Team

Re: Average and Count in same Graph

Hi @mchristy,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mchristy Regular Visitor
Regular Visitor

Re: Average and Count in same Graph

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.

Community Support Team
Community Support Team

Re: Average and Count in same Graph

Hi @mchristy,

 

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:

11.PNG

 

Query:

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

 

Result:

12.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mchristy Regular Visitor
Regular Visitor

Re: Average and Count in same Graph

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

Community Support Team
Community Support Team

Re: Average and Count in same Graph

HI @mchristy,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |