2 Replies Latest reply on Oct 9, 2018 11:09 AM by craig Webb

# Divide Total of a Meaure by Number of Weeks selected from filter

Hello,

Very new to tableau here so not sure how to title this question. I have a data base with sales, i have fiscal week as a filter so the user can select what ever fiscal week they would like to look at and see the sales. I would like this to be average weekly so if they select multiple sales, it would show average weekly. I cannot as far as i can see use a calculated field for average because, the data base is at store level, but each store belongs to a banner, a region, and then national. I want to show average weekly for each level and if i use a calculated field on this data structure it only provides average wkly sales by store if i try to do it at a national level

Even if you don't follow what i am saying above, is there away to divide sales by the number of weeks that a used selects in the filter?

• ###### 1. Re: Divide Total of a Meaure by Number of Weeks selected from filter

Hi Craig -

first welcome to TAbleau and the forum- we like to see the workbook see what is going on - if you post it with the book the answer can be more specific

in general - if you want to calculate the average of sales over a number of weeks - you can use sum(sales)/countd([weeks])

then you can look at different levels in a number of ways - the most straight forward is to create you viz at the level you want to see

Lod's will allow you to create a different virtual level in your data - fixed will create permutations of the dimensions that precede the colon and then aggregate them by what follows - they are not aggregated in themselves so something like     sum({fixed [region],[store] ,[put in as many dimensions as you want]:sum(sales)})/countd(weeks)

would get averages - since I don't have your book you may need to play with the the syntax

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Divide Total of a Meaure by Number of Weeks selected from filter

Perfect thanks so much Jim. Sum sales/ countd (weeks) works great. So simple now that I see it.