9 Replies Latest reply on Dec 14, 2016 9:18 AM by David Li

    Separately Filter Dimensions for Same Measure in Same Worksheet

    Evan Campbell

      I have a measure COST. I then have two separate dimensions. One that I need to filter to Research and the other I filter to Software. I need them on the same worksheet and to get their totals to divide each year total to get a percentage. Is there any possible way to do this?

       

      Thanks!

        • 1. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
          David Li

          Hi Evan, you should be able to do this with LOD calculations. You'll need to make a calculation that gives the year number. Then, try this:


          For the Research value:

          { FIXED [Research], [Year] : SUM([Cost]) }

          For the Software value:

          { FIXED [Software], [Year] : SUM([Cost]) }

          You can divide each of these by the total to get your two percentages:

          { FIXED [Year] : SUM([Cost]) }

          Put both [Research] and [Software] into your filters shelf and filter as desired. But make sure that they're not context filters.

          1 of 1 people found this helpful
          • 2. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
            Evan Campbell

            Hi David!

             

            Thanks for the help!

             

            So I see what you are saying by doing the fixed calculations (I am also fairly new to Tableau so hopefully I fully understand). However, the filters seem to aggregate as an "AND" statement if that makes any sense.

             

            For instance, I am making a bar graph. The columns are 'YEAR' and sit on the x-axis. The y-axis is 'COST'. I have a dimension called 'SYSTEM' and I want to filter to 'Software'. I also have a dimension called 'APP' and I want to filter to 'Research'. Now when I put both dimensions in and filter, it returns back to me the cost of 'Software' AND 'Research'. How could I separate those out so I could just get the cost for the dimension 'SYSTEM' and the cost for the dimension 'APP', separately.

             

            Is that what you described above?

             

            Thank you so much again!

            1 of 1 people found this helpful
            • 3. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
              David Li

              Hi Evan, while the filters do behave as a logical AND, using a level-of-detail (LOD) calculation with the FIXED keyword skirts the issue. Basically, a FIXED LOD calc finds all records that are similar along certain dimensions (the ones specified in the dimensionality argument, which comes before the colon) and then does some aggregation on them. For instance, let's look at this one (note that I've updated the dimension name):

              { FIXED [SYSTEM], [Year] : SUM([Cost]) }

              Tableau goes through each record of the data source and figures out its SYSTEM and Year value. Let's say a record has SYSTEM = "Software" and Year = 2016. Tableau finds all other records where this is also true and then sums up the values from the COST field for all of them. It stores that value in each matching record as a new field. So if the total 2016 cost for Software is $1000, then $1000 appears in each record for this new field.

               

              It does essentially the same thing for the second calculated field:

              { FIXED [APP], [Year] : SUM([Cost]) }

              This time, every record where APP = "Research" and Year = 2016 has the same value.

               

              Then, when we apply the filters, even though we have a logical AND, we still get all the necessary values because they've already been computed in the LOD before the view was constructed. We just need to add those two fields (and divide by the total) separately to the sheet to see the total system cost vs the total app cost.

              • 4. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
                Evan Campbell

                Hi David,

                 

                This is great! Thank you so much for the help! I feel like I almost have this down.

                 

                So I see how I can use the LOD calculation { FIXED [SYSTEM], [Year] : SUM([Cost]) }

                However, I am getting a little lost with the filtering aspect. Do I put 'Software' in where 'SYSTEM' is in the calculation and the put '2016' where 'YEAR' is? Or do I filter for 'Software' after I apply the same calculation above?

                 

                I am still trying to obtain these separate graphs on the same worksheet.

                 

                I put both LOD calculations into Tableau. However, when I try to filter it will still logical AND the connect the filters together which is what I dont want. I just want one graph to have 'YEAR' and 'SYSTEM' and the other graph to have 'YEAR' and 'APP' in the same worksheet.

                 

                Thank you again so so much David. You have helped a lot so far!

                • 5. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
                  David Li

                  You don't need to replace the fields in the LOD calculation with their values. You may need to change them if they don't match the field names in your workbook, though. You only need to specify those in the filters. Take a look at the attached workbook (Tableau 10.1).

                   

                  1 of 1 people found this helpful
                  • 6. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
                    Evan Campbell

                    You're a life-saver David! I have learned a lot more about Tableau now thanks to you!

                     

                    I am basically at the end now and just have little things to work out.

                     

                    The only thing I did notice is after I applied the FIXED functions and used my filters, the YEAR x-axis went from showing data from 2010-2016 to 2010-2012 only. Would you by chance know went wrong there?

                     

                    Also, it seems like when I inputted the functions it kept them as their names. I tried to give them aliases but that didn't work. Any idea?

                     

                    Thanks in advance!!

                    • 7. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
                      David Li

                      I'm glad you've learned a lot! LOD calculations are extremely helpful and they're my go-to tool when people have problems.

                       

                      As for the filtering, I realize now that my solution may leave out data if your data isn't dense enough to have a record that includes both APP = 'Research' and SYSTEM = 'Software' for every year. Sorry, I should have seen that coming before. There are a few ways around this. One, if you can edit your data source, you could add in some empty entries for each year to make sure that a record exists for whatever combination of APP and SYSTEM you would want to look at.

                       

                      Otherwise, you may have to resort to using parameters, which is basically the only way you'll be able to make OR filters across dimensions. What you would do is create two parameters (one for APP and one for SYSTEM) and then create a calculated field like this:

                      [APP] = [APP Parameter] OR [SYSTEM] = [System Parameter]

                      You would put that into the filters shelf and include TRUE. You would use the same calculations as before to get the APP and SYSTEM cost totals separately.

                       

                      The main limitation of this system is that the parameters won't automatically update their available options if the members of the APP and SYSTEM dimensions change. You could allow free string entry to try to account for this, but it really isn't as flexible or user-friendly as using quick filters.

                      • 8. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
                        Evan Campbell

                        Hi David,

                         

                        I guess I am just a bit more confused on this.

                         

                        I make the graphs separately in different worksheets and they return values for every year. But when I get them on the same worksheet it will only populate the first 3 years.

                         

                        Are you saying I need to go back to the data source and populate more fields?

                         

                        Thanks!

                        • 9. Re: Separately Filter Dimensions for Same Measure in Same Worksheet
                          David Li

                          Well, putting in empty values for each year is one way of fixing the issue here. But you can avoid having to do that by trying the parameter-based filtering approach I also mentioned.