10 Replies Latest reply on Oct 18, 2018 12:47 AM by Lieke Vermunt

Sum and Average based on selected filter

Hi ,

I have product type in filter and i have calculated average daily sales .now i wanted to filter my average daily sales by product type.

below is the example.

lets say...Under product type there are three types . for each type again there are 3 subtypes.

lets 3 product types are furniture,office supplies and Technology

if i selected furniture average should come .if i selected furniture and technology.....sum(avg(furniture)and avg(technology) should come. i think looks like a pretty basic calculation but cant figure out.

I tried with a below calculation

((sum([sales])/COUNTD([Sub category])/[nOOF DAYS ytd]))

No of days calculation:

DATEDIFF('day',MIN([Date]),MAX([Date]))+1

this calculation gives correct value if one product is selected but if i selected more than one , it is giving average ,i want some.please help me

Thanks

Yamuna

• 1. Re: Sum and Average based on selected filter

Hi Yamuna,

First let me read the formula that you mention ((sum([sales])/COUNTD([Sub category])/[nOOF DAYS ytd])), it mean for all the sales operation identified by filters find the average sale per each subcategory per each day ... then you try to add filter to include one or more category and you get unexpected value.

first make sure to exclude the category from your calculation, like that:

{ exclude [Category] :

SUM([Sales])/COUNTD([Sub-Category])/

(DATEDIFF('day',min([Order Date]), MAX([Order Date])) + 1)

}

Then add filter on category level (Product in your case), and try to select on category or more and test if this is the required result ... see the below images

Note:

If you found this useful please mark it as correct answer or as helpful post ...

Best Regards

• 2. Re: Sum and Average based on selected filter

Thanks for your reply.

this is the output when i was selected furniture.

And this is the output for Office Supplies

If i selected both this is the output. this is not my required output.it is averaging.when two are selected i want to show sum.if it is one it should be average ...

the output should be 182.3

Thanks

Yamuna

• 3. Re: Sum and Average based on selected filter

Hi Yamuna,

Ok, you can make a simple change to get what you need,

{ INCLUDE  [Product Category] :

SUM([Sales])/COUNTD([Product Sub-Category])/

(DATEDIFF('day',min([Order Date]), MAX([Order Date])) + 1)

}

but do not add the [product category] on row, or column shelf this to avoid dividing them, also use the SUM in the aggregation as shown in the below images:

Best Regards

• 4. Re: Sum and Average based on selected filter

Thanks Haitham,

In above calculation i forget to include one more filter.if sub category contains sub filter,how the calculation will vary.

Thanks

Yamuna

• 5. Re: Sum and Average based on selected filter

Here iam attaching sample data for your reference.

• 6. Re: Sum and Average based on selected filter

Hi Yamuna,

Filtering on sub category will work normally, filtered sub categories will be eliminated from the calculation ... I also tried your sample data with sub category filter, it seem also ok for me ...

Note:

If you found this useful please mark it as correct answer or as helpful post ...

Best Regards

• 7. Re: Sum and Average based on selected filter

For me its not giving right output what i required.

iam attaching sample workbook.

• 9. Re: Sum and Average based on selected filter

Hi Yamuna,

I could not see the problem in your workbook ... as it give me the following results:

Furniture            51.39

Office Supply     74.52

Technology        115.7

Furniture/Office Supply       125.9

Furniture/Office Supply/Technology          241.6

Also the sub category filter work normally if I remove the (Furnishing sub category 63.11) the daily average of the (Furniture  Category) become 48.28 and the total become 238.5 which is the exact difference between (51.39 and 48.28), do not forget you work here with average removing item may not always decrease the value it can increase it if you eliminate high value items ...

What exact is the problem?

Best Regards

• 10. Re: Sum and Average based on selected filter

Hi Haitham Farouk,

Is it also possible to link this to a legend selection, in stead of a filter? Or link the filter used to a selection in the legend?

I'm building a dashboard in which I would like to change the numbers when the using clicks on a certain type.

Kind regards