1 2 Previous Next 22 Replies Latest reply on Sep 17, 2014 2:30 PM by Shawn Wallwork

    Please help with filter actions and table calculations

    Kes Harding



      I'm trying to build a dashboard showing a calculated measure called Sales Density (sales per week per square meter of shop floor) over time, where you can use a filter action to display a branch, and also display the average for all branches as a comparrison.


      Obviously if you use a filter action you filter all the other branches out and your average doesn't work any more.


      I'm sure you can get round this by duplicating the data source. So you filter the branch out of your primary source, but leave all the branches in the secondary to calculate the average. In order to do this I unlinked the branch code field in the secondary data soure (otherwise it still filters the other branches out.)


      However this then returns null as you can see in the attached package workbook. How do I get this to work?


      Thanks in advance!



        • 2. Re: Please help with filter actions and table calculations
          Sourabh Dasgupta

          Hi, you can create a table calculation on your Branch field and use it in filter


          1) Calculated Field = Lookup(ATTR([Branch]), 0)

          2) Bring this field in the Filters

          3) For average you can use the Window_Avg function.

          4) Filtering values from the first calculation does not affect the average


          Let me know if it helps

          • 3. Re: Please help with filter actions and table calculations
            Kes Harding

            Hi Sourabh,


            Thanks for your repsonse. I had already tried this approach before I attempted to solve the issue by using a duplicated data source. I gave it anothrer go just to be sure but the problem is applying a filter action, as opposed to a regular filter. If you see the attached workbook I've created the lookup field as per your suggestion, but because the result is a measure and not a dimension it does not appear in the list of available fields when you try to apply a filter action. See below.


            Filter Action.PNG




            • 4. Re: Please help with filter actions and table calculations
              Matt Lutton

              I don't believe this (the missing field in your action filter) is because the field is a measure, as it is appearing just fine in your Source sheet field list.  There's another explanation, but I'm not quite clear on what it is or why it appears in the Source sheet, but not the Target (even if we use the field in both sheets).


              You want the branch to filter the "Branch Sales Density" part of the line chart, but not the "Average Branch Sales Density" part--is that correct?  If not, can you re-state the overall goal for me?


              My understanding is that because your measures are Table Calcs, the duplicate data source technique will work.  This has to do with what Tableau is sending to the data source behind the scenes, and the order in which those procedures are carried out. I don't have all the details on WHY, but perhaps jonathandrummey will have time to take a look and school us all on this example?  I'm also confused on why the LOOKUP field appears in the source sheet for the action filter, but not the target sheet.  I've seen this before but its still not clear to me.


              Aside from all of that, is creating another separate sheet for the Overall Sales Density Average an option for you?  It would look nearly identical to what you have in the dual axis line chart, except there would be two sheets.  Then, you could apply the branch code as an action filter to only the one sheet.



              • 5. Re: Please help with filter actions and table calculations
                Matt Lutton

                Do you want Sheet 3 to be filtered by the action?


                It would help me to have some clarity on exactly what you want to happen when a branch code is selected from the "Branches" sheet--should the action only filter the Density by Week sheet?  What is the purpose of Sheet 3--a text table of one branch, filtered by the action?  Or is it supposed to also show the Overall Average?



                • 6. Re: Please help with filter actions and table calculations
                  Kes Harding

                  Hi Matthew,


                  Yes you're correct: I want to filter the branch sales density but not the average branch sales density. So you click on a branch and then you see how it has performed over time against the average.


                  Sheet 3 is not meant to be part of the final viz, it's purely a test. If the filter action is not applied then it shows the correct value (which doesn't change as you click on different branches.)


                  I had actually intended to have use a dual axis chart rather than a two separate axes. I think that was lost in my haste to switch the second workbook to the lookup method. In the first one I posted (where the averages come back as null) I've used dual axis. Having a dual axis makes it much clearer when a branch has performed above or below average.




                  • 7. Re: Please help with filter actions and table calculations
                    Matt Lutton

                    Let me know if this is anywhere close to what you want; I've set up 4 sheets; no LOOKUP is used in this example, just the dimension Branch Code is used for action filtering.


                    I have separate sheets for the "BranchDensity by week" and "Overall Avg Density".  In the overall Avg Density, branch code is required to get accurate results with your table Calc [Average Branch sales Density], but we do not have to apply the action to that sheet.  In sheet 3, I assumed you wanted to filter the sheet by a single branch code.  I used the titles of the sheets to show what branch is displaying, for clarity.


                    I also switched "Fin Week" to a discrete dimension, as with a continuous Fin Week dimension on the Overall Avg Density, the line was getting broken up when a week did not have a value.  I'm not clear on why that is, or if there is a better way.




                    Hoping to get some clarity on some of the other aspects of this thread.  Let me know if this gets you anywhere close to where you need to be.

                    • 8. Re: Please help with filter actions and table calculations
                      Matt Lutton

                      Ahh... yes, a dual axis would make comparing the two much simpler.  So, my above workbook may not help.  I'm hoping to learn from this thread--just know there are a lot of factors at play here, and Tableau does not make it immediately obvious to us what is happening or why.  So, we must study these factors and ask questions of others to help in understanding.


                      Hopefully we'll get some feedback from some others who know more about Tableau's internal workings. I'll take a look again if I get time today.

                      • 9. Re: Please help with filter actions and table calculations
                        Kes Harding

                        Thanks for your contribution Matthew.


                        The sales density calculation is a real awkward one because of store space being non-additive. I'm sure this would be a lot easier if there measure wasn't a table calc!


                        I still feel like it should be possible to make it work, but it's getting the understanding of how Tableau is working this under the hood that's the key.


                        It's only twenty minutes til home time here in the UK so I'll probably pick this one back up in the morning.





                        • 10. Re: Please help with filter actions and table calculations
                          Kes Harding

                          So I thought I'd cracked it....but alas no!


                          I realised I could create the first measure - branch sales density - without resorting to a table calculation by dividing the store space by the number of records. As long as this is always calculated at a branch level it works fine.


                          sum([Sales ex VAT]) / (sum([Square Meters])/sum([Number of Records])) / countd([Fin Week])


                          But I still can't get my window_avg to work from my secondary data source. I can't really work out what it's doing, but it's definitely not right!


                          What I want it to do is as follows:

                          • From the primary data source calculate the sales density for a single branch, chosen by a filter action from the Branches sheet.
                          • From the secondary data source calulate the sales densitites for each branch and then calculate the window average of these.
                          • Blend the two together by Fin Week but not Branch Code.


                          I feel like what I've done in the attached ought to do this, but it doesn't. Does anyone know what it is doing, and how to get it to do the above?

                          • 11. Re: Please help with filter actions and table calculations
                            Matt Lutton

                            Hi Kes:


                            Do you have an idea of what value you expect for the Average Branch Sales Density in a given Fin Week?  That may help me to get at a solution, as I am finding it difficult to follow everything you've done, or why you've done it (like the copied data source--why are we using this?  I know we can sometimes use a duplicate data source without a blend on a dimension, to get results at another level--but since this is a Table Calculation, which is dependent on the dimensions in the view, I don't think these concepts work well together).  I'm still learning about these techniques myself, and would welcome someone with more experience to jump in and help us both!



                            • 12. Re: Please help with filter actions and table calculations
                              Kes Harding

                              Hi Matthew,


                              The last sheet at the bottom shows the correct values, which is why I put it there! When a can get a second line on the line graph to consistently show those values I will be cracking open the champagne! Or at least the prosecco...


                              So what I want is to calculate sales density for each branch and week, and then work out the average between the branches and plot that by fin week.


                              I'd rather not have a secondary data source, but I've been trying it to get around the issue of filtering out branches from the average. The average should stay constant regardless of the branch filter action.


                              I think I'm now getting why table calcs won't work. I want them to be applied to the secondary data source before the data blend, but I get the impression that Tableau blends first and then does table calcs.





                              • 13. Re: Please help with filter actions and table calculations
                                Matt Lutton

                                Yes, the blend happens before Table Calcs, you're right about that


                                I'll keep looking when I have time.  I'm sure it'll get worked out eventually, I sometimes just need to walk through what all is happening in a dashboard to understand why certain things are included.  Plus, I'm usually multi-tasking rather than focusing on one task at a time!    

                                • 14. Re: Please help with filter actions and table calculations
                                  Kes Harding

                                  I know the feeling! Any and all help greatly appreciated

                                  1 2 Previous Next