will not take real average--only "counts"

[sy898661]

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!

[Anonymous]

Hi @sy898661

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

Thanks

Raj

[sy898661]

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!

[sy898661]

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!

[Anonymous · Accepted solution]

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 🙂

Thanks

Raj

[elinv]

What about measures? I have a percentage but if I change it to a whole number it stays the same and I still can't choose Average (see below). 

Thanks!

[Ashish_Mathur]

Hi,

See my reply here - Re: Make measure show Average not Sum.

[sy898661]

YES! Thank you SO SO much!!!