cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sy898661 Member
Member

will not take real average--only "counts"

Hello!

 

I have a set of data including a column for Impact Score (1, 5, or 9), Facility (A, B, C, D) and Crash Codes (9 different codes). The data set has 50+ rows of information regarding these areas over time (for example, Facilty A may have 4 differnt crash codes with scores of 1, 9, 9, 9), and I am trying to create a visual for "Average impact score per crash code" as well as "Average impact score per facility". When I try to create the visual, it automatically changes it to "Count of Impact Score" and will only return how many different scores there are in each category, instead of giving the average. I feel like I've tried everything settings wise to correct this, including making a new measure using the average function, but it still does not output the data I am looking for.

 

I know the data is incorrect because for Facilty D, there are 10+ scores of 9 and only one score of 1, but it is outputting an average score of 2.

 

I'm sorry if this is a confusing question, but I would really appreciate some help if anyone understands what I am asking for!

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: will not take real average--only "counts"

HI @sy898661

 

This is because your column would have defined as Text datatype, thats why you are not getting other aggregate options. Check and convert the Score column as Number and try, you should be good for the weekend Smiley Happy

 

Thanks

Raj

5 REPLIES 5
sy898661 Member
Member

Average, not Count

Hello!

 

I have a set of data including a column for Impact Score (1, 5, or 9), Facility (A, B, C, D) and Crash Codes (9 different codes). The data set has 50+ rows of information regarding these areas over time (for example, Facilty A may have 4 differnt crash codes with scores of 1, 9, 9, 9), and I am trying to create a visual for "Average impact score per crash code" as well as "Average impact score per facility". When I try to create the visual, it automatically changes it to "Count of Impact Score" and will only return how many different scores there are in each category, instead of giving the average. I feel like I've tried everything settings wise to correct this, including making a new measure using the average function, but it still does not output the data I am looking for.

 

I know the data is incorrect because for Facilty D, there are 10+ scores of 9 and only one score of 1, but it is outputting an average score of 2.

 

I'm sorry if this is a confusing question, but I would really appreciate some help if anyone understands what I am asking for!

 

Thank you!

Highlighted
Super User
Super User

Re: will not take real average--only "counts"

Hi @sy898661

 

I hope you have selected the Average option in Visualization- > Fields Tab-> Value-> Right click on Score and select "Average".

 

Average.PNG

 

 

Thanks

Raj

sy898661 Member
Member

Re: will not take real average--only "counts"

Hi Raj,

First off, thank you for taking the time to try and help!

I just looked, and those options are not showing up on my PBI Desktop or on the Service, it only gives the option for "Count" and "Count (Distinct)"

Thanks!

Super User
Super User

Re: will not take real average--only "counts"

HI @sy898661

 

This is because your column would have defined as Text datatype, thats why you are not getting other aggregate options. Check and convert the Score column as Number and try, you should be good for the weekend Smiley Happy

 

Thanks

Raj

sy898661 Member
Member

Re: will not take real average--only "counts"

YES! Thank you SO SO much!!!