Indicator needs to show AVG for all values as secondary

Comments

5 comments

  • Avatar
    Michael Becker

    Hi Javier,

    to do this you need to use ALL function, so your secondary function would look like this - (AVG(Sales),ALL(Country))

    Regards,

    Michał Becker

    0
    Comment actions Permalink
  • Avatar
    Javier Irazazabal

    Hi,

    Seems it's not working properly. Data does not equal:

    When doing a AVG(Country,SUM(Sales)) -right data- with Country filter disabled for this widget I get:

    That's the right vlaue. But when using (AVG(Sales), ALL(Country)) as secondary, I get:

    That's wrong! It's not working as exepcted. In last figure, in AVG, the value must be 1,83M.

    It's not working as expected

    0
    Comment actions Permalink
  • Avatar
    Hamza Jap-Tjong

    Hi Javier,

    The 2 formules are doing something differently. That is why you do not get the same result.

    AVG(Country,SUM(Sales)) calculates the average sales grouped by country. Example:

    Country A sales: 1M, 2M, 3M. Total sales = 6M

    Country B sales: 2M, 4M, 6M. Total sales = 12M

    Result of formula = 6M+12M /2 = 9M

     

    (AVG(Sales), ALL(Country)) calcultaes the average sales but takes in to account all countries without grouping them. 

    Same example but now it is 1m+2m+3m+2m+4m+6m / 6 = 3 M

     

    You could try something like

    (sum([Sales]),all([Countries])) / (count([Countries]), all([Countries]))

    (although the last all(countries) should probably be a different variable, however I do not know your dataset)

    0
    Comment actions Permalink
  • Avatar
    Javier Irazazabal

    Hi,

    AVG(Country,SUM(Sales))  is desired result. Others are not right.

    BR

    0
    Comment actions Permalink
  • Avatar
    Gowtham Senthilkumar

    Javier, 

    In the expression AVG(Country,SUM(Sales)) - Click on the Country and choose 'Edit filter'. Now you have many options to go about: 

     

    If you choose 'List Filter' -> Unselect all the members in the list and start selecting manually all the countries in your list. 

    If you think, this is not a scalable method, choose 'Text Filters' and perform the following hack - Choose 'Doesn't equal' and then type a special character like '%' - this way, you are hard selecting all the countries in your list. 

    Then, click on "OK" on the filter wizard and then save the formula. Now you should be seeing your AVG working irrespective of your filters. 

    Important Note: The table & column of the Country dashboard filter and the Country field that is used in AVG formula should be one and the same. 

    Hope that helps. 

     

    Thanks, 

    Gowtham Senthilkumar

    0
    Comment actions Permalink

Please sign in to leave a comment.