Forum Discussion

sy898661's avatar
sy898661
Icon for Helper V rankHelper V
8 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      sy898661
      Icon for Helper V rankHelper V
      8 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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