Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Basic stats for a given field

If accessing a database directly, as opposed to via powerbI, I could find info about a given field by running a bit of SQL - for instance: 

 

SELECT AVG(field_name) AS averageVal FROM tableName

 

I could then save that into a variable and display it to the user as needed. 

 

In powerBI I can display the data in a table, but can not yet find out how to run these kind of queries on the data...I'm sure it must be possible....can anyone help? 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Modify your measure as below:

    Measure = 
AVERAGEX(ALL('Table'),VALUE('Table'[values]))

    And you will see:

     

    For the related .pbix file,pls see attached.

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Create a measure and you can use it

    measure = AVG(Table[field_name])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks! 

      I had tried that, but received the error that average could not operate on string values. 

      All of the values in the column are numeric - but it may be that they are stored in the database as numeric striungs rathert than as integers. Not sure if there is a way I can cast them to ints....

       

      I tried adding a measure that called VALUE() on each numeric field - but there seems to be some kind of syntax issue 

       
      It seems I can't add a calculated colum to the data, since it is live streamed from a remote database 
       
      Reregarding the conditional cloauses - so the equiavalent of: 
      Measure = AVERAGE(events[param1]) where date range is X for instance
       
      It looks like I have to enable the time field, then  add the filter, then disable the field again? That seems clunky, though it does work, so not sure if there is a neater way!

       

       

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    you can either do a measure as amitchandak already mentioned or you can just drop the column in a table and in the options of the value you can change the aggregation in this table:

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Ah - I think the problem is that though these values are numeric, th data source stores them as strings

      Is there a way I can tell powerBI to treat these numeric strings as numbers? Unfortunately I don't think I can change the remote data source....

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi  Anonymous ,

         

        Modify your measure as below:

        Measure = 
AVERAGEX(ALL('Table'),VALUE('Table'[values]))

        And you will see:

         

        For the related .pbix file,pls see attached.

         

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!