8 Replies Latest reply on Feb 6, 2018 1:43 PM by Shinichiro Murakami

# Dynamic table calculations

Hi everyone,

I have attached an excel workbook with a sample of my data structure and what I am trying to achieve. The existing sheet I have in Tableau is a table with property rows and store columns and the measure being displayed is the sum(sales) for each store at each property. I need to be able to select a "denominator store" to divide the sales of all the other stores by. I.e. maybe I have a store selection parameter and if I choose store 'af' then the sales of the other stores are displayed as a percentage of 'af' sales. I also only want to display properties where greater than 50% of stores have sales. Fyi - with the real data there are many more stores and the user will also be choosing which stores they want to display in the table. I hope this makes sense, please ask me any questions for further clarification.

Thanks in advance for any assistance with this!!!

• ###### 1. Re: Dynamic table calculations

HI Lauren

Hope this helps.

Store % vs selected sore

In sample data, there are no "0" sales and filter does not filter out anything.

Or if you suppose that all Property has 5 Stores, the story becomes completely different.

Regards,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Dynamic table calculations

Hi Lauren,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

1. M1. Fixed: {fixed [Property]:sum( if [Store]=[Store Parameter] then {fixed [Store],[Property]:sum([Month Sales])} end)}

2. M2. %: [Month Sales]/[M1. Fixed]

3. D1. Display: {fixed [Property]:count([Store])}/{fixed: max({fixed [Property]:count([Store])})}>=0.5

4. Drag the required objects to the indicated locations.

Regards,

Norbert

1 of 1 people found this helpful
• ###### 3. Re: Dynamic table calculations

Hi Shin,

Would you be so kind to explain why did you use MIN in calculation Store % vs selected sore?

D

• ###### 4. Re: Dynamic table calculations

Thanks Shin this is really helpful, however my results are slightly off. Might it have something to do with the fact that I am also filtering by date?

• ###### 5. Re: Dynamic table calculations

Hi Lauren,

If you are using filter, LOD does not correctly calculate unless you change the filter to context.

And cold you mark my answer as correct to closer the thread. not from inbox view, but from original post.

Thanks

Shin

• ###### 6. Re: Dynamic table calculations

Hi David

max, min attr, avg anything works because there is only one value.

min is my own rule in this situation. Actually using Max makes me  uneasy when I have incorrect formula. It's MAX anyways...  extreme min is usually 0, but extreme max is infinite.

Thanks,.

Shin

1 of 1 people found this helpful
• ###### 7. Re: Dynamic table calculations

Yes I thought that adding my date filter to context would ensure it applies this date to the whole sheet but it is not working. When I remove the date filter, the percent vs parameter looks good but my sales are summed for the entire date range...

• ###### 8. Re: Dynamic table calculations

Please attach packaged workbook with failure/ and clarify the issue with illustration...

Thanks,

Shin