• 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

• 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

• 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)

• Hi,

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

BR

• 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