Friday, February 1, 2013

Dynamically Choose the Fields Displayed on a Chart in Tableau

Today, we will talk about using parameters to change the fields that are displayed on your chart.  For instance, let's say you have 5 metrics for each of 6 measures.  You don't want a chart with 30 columns; so you need to allow the user to select the measure and only see metrics associated with it.  You could filter on "Measure Names", but this will quickly become unwieldy.  As usual, we will the Superstore Sales sample data set in Tableau.

"Measure Names" Quick Filter
Step 1:
  • Create a Parameter named "Measure Parameter"
  • Give it the data type "String"
  • Select "Allowable Values: List"
  • Enter your list of measures, e.g. "Sales", "Profit", "Quantity"
Measure Parameter
Step 2:
  • Create a Calculated Field named "Measure"
  • Enter the CASE Statement as below
Measure
Now, you have a Calculated Field that takes a specific measure based on which value the parameter takes.

Step 3
  • Use "Measure" in place of the Standard Measures
  • Right-Click "Measure Parameter" -> Select "Show Parameter Control"
Chart with Dynamic Measures
Now, you can select which measure you want to display, and your chart changes in real time.  There is so much to be done here.  You can even change your titles to display dynamically as well.  Feel free to explore the multitude of possibilities.  Thanks for reading.


Brad Llewellyn
Associate Consultant
Mariner, LLC
llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

60 comments:

  1. What if I want to have several measures go into the rows field? Case when "Web" then measure 1, measure 2, measure 3, etc?

    ReplyDelete
    Replies
    1. All you would need to do is create a new calculated field for each measure. You can link them all to the same parameter.

      For example,

      Measure 1:

      CASE [Parameter]
      WHEN "Volume" THEN [Volume Sold]
      WHEN "Price" THEN [Sales]
      END

      Measure 2:

      CASE [Parameter]
      WHEN "Volume" THEN [Volume Purchased]
      WHEN "Price" THEN [Purchases]
      END

      Delete
    2. I did this- I have web, mobile and both... but when web is selected, the web fields are showing, and the mobile fileds have a presence but are nulls. Do you know how to hide the web when user selects mobile, and hide mobile when user selects web? Thanks for replying- you have been very helpful.

      Delete
    3. I think I understand your issue. Could you send me a mock-up or .twbx file so that I can help you specifically? If you need to, you can email me directly at brad.llewellyn@mariner-usa.com

      Delete
    4. Hi Brad, I have a similar issue with hiding/showing the columns. There will be two columns a and b; a belongs to sales and b to profit. When i change from profit to sales, i intend to display only column b. In this case, both a and b columns are displayed but the data in column a is blank. Hope you understood my question, please let me know your valuable inputs. Thanks!

      Delete
    5. Hi Brad, I have a similar situation. I will explain it with an example, consider column a is shown on the selection of sales and b for profit, i want only column b to show when profit is selected. In this case, both the columns(a&b) are displayed but data is present only on b and a is blank. I want to hide a and display only b and vice-versa when sales is selected. Looking forward for your valuable reply. Thanks!

      Delete
  2. sort is not working when you swap the measure

    ReplyDelete
    Replies
    1. Thanks for your reply!!! Could you be more specific? Perhaps sending me a mock-up and/or .twbx?

      Delete
  3. thanks for your replay. Let me explain here.
    1.Meausre % -> if parameter_type = 'X' then Measure1 elseif parameter_type ='Y' then Measure2 end
    2. Dimension -> Company

    I am using bar chart to show highest % to lowest % company.
    when I am change the parameter type from X to Y or Y to X then the sorting is not working.

    ReplyDelete
  4. pls check this.
    http://community.tableausoftware.com/thread/127344

    ReplyDelete
  5. I can choose the fields dynamically and try to change filter name (title),but I cannot do that. Assume that there are 2 different features for 2 product groups. Ex: Features of Washing Machine is "loading capacity", "energy label" and features of Dishwasher are "number of programs", "number of place sets". The first features are "loading capacity" and "number of programs". First of all, product group is selected and I can show the "Measure 1" as "Product Group Name" ; but the second selection is different for all product groups. When a product group is selected, its features are displayed but title is not flexible.I don't want to see "Measure 2" as filter name. Instead of that, it should be seen as "loading capacity" when Washing Machine is selected and "number of programs" when Dishwasher is selected. Is it possible?

    ReplyDelete
    Replies
    1. I think I understand what you were asking. I "cheated" by using a text box on a dashboard. You can download it here.

      http://speedy.sh/gTa5d/Book2.twbx

      Let me know if this helps,

      Brad Llewellyn
      Associate Consultant
      Mariner, LLC
      brad.llewellyn@mariner-usa.com
      http://www.linkedin.com/in/bradllewellyn
      http://breaking-bi.blogspot.com

      Delete
    2. I cannot open this file. Could you please write the steps that you follow?

      Delete
    3. Try this link. It was made in Tableau 7.

      http://speedy.sh/BP6Cx/Book2.twbx

      Thanks,

      Brad Llewellyn
      Associate Consultant
      Mariner, LLC
      brad.llewellyn@mariner-usa.com
      http://www.linkedin.com/in/bradllewellyn
      http://breaking-bi.blogspot.com

      Delete
    4. Thanks for your reply! it will be useful for me

      Delete
  6. thank you for your post......
    But i have a small doubt....
    For example I have state in state some districts, in districts some cities ,
    when i mouse hover or click on particular district cities will displayed only for particular district.... can any one help ...thanks in advance

    ReplyDelete
    Replies
    1. Thanks for the comment! I'm not sure I understand what your problem is. Any chance you could send me a package workbook or .twbx file?

      Delete
  7. Hi Brad, Thanks for your valuable post. It was very helpful. The issue that I am facing is that if the two values to be displayed have different data types ( say, number & percentage ), is there any simple way to define the datatype of the resultant calculated field? ( In your example, if sales / quantity are numeric but profit is a percentage ). Thanks for your time and insights!

    ReplyDelete
    Replies
    1. To be honest, I'm not sure. I imagine that there is not a simple way. Data types are not programatically define in Tableau. Perhaps you should ask this on the Tableau and see if someone there knows. Let me know if you find a way.

      Delete
    2. Thanks for your response. I would try asking on the Tableau forum and let you know if I get a positive response. Do you know any active tableau forums that you think would have answer to this question?

      Delete
    3. http://community.tableausoftware.com/welcome

      Delete
    4. Thanks Brad. The forum was helpful and I was able to get the solution to my problem. The discussion is at the following URL for your reference :

      http://community.tableausoftware.com/message/238576?et=watches.email.thread#238576

      Delete
    5. Thanks! I had a similar idea. I will warn you that solution will not scale well because there is alot of overhead in turning everything into text. I love the idea though!

      Delete
  8. Hi Brad,
    I am looking for what Kuber Nagpal wanted and went over the forum which he has shared but I need to dynamically change the data type format between 2 metrics in a chart. Any workaround?

    ReplyDelete
    Replies
    1. Kuber Nagpal linked this forum post which has a possible workaround.

      http://community.tableausoftware.com/message/238576?et=watches.email.thread#238576

      Delete
  9. Hi, I am newbie, I want to show my values in different column, like I have product category and their sales and target and the previous year sales and target, I want to show percent of Target Achieved also. But when I am trying to put values in tableau sheet, all the values related to product category comes in same column such that



    product 1 5%

    60%

    30%



    ReplyDelete
    Replies
    1. If I understand you, you're saying that when you put multiple field on the Text Shelf, you get everything inside the same cell. What you need to use is the Measure Names/Values combo. Let me know if you find this article helpful.

      http://kb.tableausoftware.com/articles/knowledgebase/measure-names-and-measure-values-explained

      If I have misunderstood your question, perhaps you could email me a mock-up or .twbx file so that I can see?

      Delete
  10. Hi Brad,

    My requirement is creating a cascading filter. suppose I select region in my region filter according to the selected value state will show. this I have already done. but problem is when I select any value in state and then change the value of the region filter then chart is blank. I need is when I change the value of region I will get the all values result according to the selected region.

    I hope you understand the problem. please help me.

    Thanks in advance....

    ReplyDelete
    Replies
    1. That's an interesting dilemma. To be honest, I don't know of a way around it. The whole idea around cascading filters is that they only go one direction. In my experience, this has always come down to training the users that they have to select "All" in the State filter before changing the Region filter. Sorry I didn't have better news.

      Delete
  11. Hi Brad

    This came in useful, but achieved only part of my issue.
    A quick background, i want to develop your ideas further.

    I have a measure "Profit" (along with other measures - 1,2,3,4...etc)
    And Act/Plan dimension (Actual, Plan)
    So I created 3 new measures, Actual Profit, Plan Profit and Variance (Actual-Plan)
    For each measure-actplandim combo

    What I want to achieve is a grid like

    Actual | Plan | Variance
    measure Profit
    measure Gross Profit
    measure 123

    Because Variance is not part of a dimension, i thought i could do this by using parameters, the trouble with parameters is that it only display what you select. Do you know of any way to display all 3 categories in 3 different columns? ie Actual Plan and Var together.

    I can't have measures running on columns because there will be multiple measures running down the rows. The data structure won't let me do this quickly because Variance is calculated and have no associated member for it.


    Thanks
    Gem

    ReplyDelete
    Replies
    1. For some kind of measures by measure table, you need to be a little more clever. The easiest solution is to just put one row or column per worksheet, and use a dashboard to put the sheets next to each other. Does this help?

      Delete
  12. I am unable to use the above solution with Table Calculation's...I would like to change the table calculation measures dynamically using the above solution is that even possible?

    ReplyDelete
    Replies
    1. Thanks for commenting! That's a very sticky area you're in. There's nothing stopping you from dynamically swapping table calculations. However, there's no guarantee that the "Compute Using" will be carried over. Can you send me a mock-up or .twbx?

      Thanks!

      Delete
  13. Hi Brad ,
    I have a simple requirement where I want to select 3- 5 metrics to be displayed in a cross tab.

    Approach 1 : I tried having a "Metric filter".
    Problem 1 : All metric fields are shown in the list like intermediate calculation fields, which i don't want. I cannot even hide the metrics because they are used in the calculations.
    Problem 2 : I cannot make this filter common across two sheets in tableau , it will work only for one sheet. If I have 2 sheets to be shown in the dashboard where one displays table and other line chart for same set of metrics it is not possible.

    Approach 2 : if i use parameter. lets say i have 5 differemt parameters to select 5 metrics.
    Problem 1 : creating 5 parameters for one table and shown them in dashboard is tacky (still i can ignore it for now)
    Problem 2 : I am not able to rename the column header according to the metric i choose. Eg. if i choose Sales I want the column header to show sales and not Metric 1 etc.

    Any help is appreciated. Thanks !!!!

    ReplyDelete
    Replies
    1. Atts123,

      Thanks for commenting! I can't tell you how many times I've heard exactly that question. Unfortunately, that's on the areas where Tableau doesn't have much functionality. The best I can say to you is that you're on the right track and that you should consider using Tableau for its strengths as opposed to trying to replicate Excel.

      Cheers,

      Brad Llewellyn

      Delete
  14. Hi Brad, i was hoping to open the link you posted on June 24th, 2013.

    "Brad LlewellynJune 24, 2013 at 2:21 PM
    Try this link. It was made in Tableau 7.

    http://speedy.sh/BP6Cx/Book2.twbx"

    As i'm have the same question and am trying to get the field names to match what is shown based on what i select with the parameter. Currently the field names just say Placeholder 1, 2, 3 etc.

    Thanks.

    ReplyDelete
    Replies
    1. Steve,

      Thanks so much for commenting! Unfortunately, these temporary sharing sites don't store stuff for very long. Could you explain the issue you are having?

      Delete
  15. This is the issue above that seems to be the same as mine...I've created CASE statements and a parameter to dynamically show certain fields. I need to put all CASE statement fields on the row self and what is displayed for each CASE statement calculated field is dependent on what i choose using the parameter function. However the name of the field stays the same as the name of the CASE statement, but i need a header to show what the column is displaying based on what I select from the parameter. For example, one parameter selection is for Region and Property, so when that is selected the CASE statement calculated fields show the data but with no header or the header of the calculated field. I hope that's clear, basically i'm hoping for dynamic headers for the calculated field based on the selection from the parameter.

    ReplyDelete
    Replies
    1. Unfortunately, I have the same answer for you as I did for them. There doesn't seem to be a way to achieve dynamic headers. Wish I had better news. Good luck!

      Delete
  16. Do you have any other suggestion for filtering for certain headers?

    ReplyDelete
    Replies
    1. Steve,

      Here's a thread from the Tableau forums that has a possible, yet clunky workaround.

      http://community.tableausoftware.com/thread/110424

      Delete
  17. Thank you Brad, this seems like it will work well actually for what i need, probably better actually.

    One question though, i'm just trying to figure out how the Parameter is controlling which sheet is in view on the dashboard?

    ReplyDelete
  18. Do you know how i could create a filter for the dashboard that is able to change which sheet is displayed within a container? That would be ideal.

    ReplyDelete
    Replies
    1. There are a couple of ways. The simplest is that you could create a calculated field that says

      IF [Parameter 1] = [Value 1] THEN 1 END

      and another field that says

      IF [Parameter 1 = [Value 2] THEN 1 END

      If you set these as filters on the respective pages, you will effectively blank out every page except the one you want. Does this make sense?

      Delete
  19. How do I dynamically change the name of the column header that is selected using the parameter?

    ReplyDelete
  20. Hi,

    I have a query that brings back data for last month and this month so far.
    I've created a parameter with "Day" and "Month" as the 2 options, and then created a calculated field so that if day is selected I see the detail for all days, and if month is selected I see the data aggregated by last month and the current month so far. This is working fine, however, I'd like to take it a bit further.

    When I select day, I want to be able to filter to view the last 7 days, but then when I select month, I want this filter to be automatically removed. Is there a way to achieve this?

    Thanks

    ReplyDelete
  21. i have a tablueau workbook and a row in an excel file is feeding the workbook. There are quick filters as well. I want the row feeding the workbook to change depending on which quick filter is used.

    For ex. filter 1 chosen, row x in excel feeds the tableau workbook
    filter 2 chosen, row y in excel feeds the tableau workbook etc

    can this be done? please help

    ReplyDelete
  22. I want to select multiple dimensions dynamically. How to do this because in parameter we can select only one value at a time?

    Thanks,
    Ashish Gupta

    ReplyDelete
  23. I have 200 measure values which need to be dynamically displayed as axes, depending on what the user chooses. Is there any way to do it? In your article, you talk about creating a IF statement for each measure, but it will be cumbersome task if I need to do it for 200 measures. Please help!

    ReplyDelete
  24. Thanks Brad for this post.

    I have a question:

    Is possible using this approach in tableau to make a dynamic crosstab using a selector of dimensions names & meassures names.

    I explain:

    In QlikView i used a Dashboard with a selector of mesaures names & dimensions names that dynamcally is added in Table as columns & rows with multiple levels.

    Can make for Tableau Reader this type of dashboard?

    ReplyDelete
  25. when we are labeling profit and quantity ,we need to show profit in currency and quantity in normal number format, is that possible

    ReplyDelete
  26. Hi, I'm trying to apply your method to dimensions selection and got an error when trying to create the calculated filed with the case of each string selected. is it even possible?

    Thank you, Nofar

    ReplyDelete
  27. Hi,
    I'm trying to apply the same method you presented, but for dimension selection. is that possible in the same method? (dynamcally choosing which dimensions will be presented in the rows?

    i tried doing so and got an error in the calculated field stage.

    Thanks,
    Nofar

    ReplyDelete
  28. Hi,

    I want a checkbox instead of a radio button.SO definately parameter is not the option.Can you please assist how can this be obtained.
    Thanks

    ReplyDelete
  29. Hi Brad,

    How can we include 'All' option to the parameter so that on selecting 'All',one can display all the measures??

    ReplyDelete
  30. Hi Brad,

    In case i want to select more than one field say sales and profit both, how can we achieve that?

    Thanks

    ReplyDelete
  31. Thaaanks, very useful!! Cheers from Peru

    ReplyDelete
  32. Is there a way to create a parameter with dozens of measures, without having to type in each one individually in the Create Parameter field as well as in the Calculated Field field? Thanks.

    ReplyDelete
  33. Hi Brad ,I have a tableau issue:
    For example,

    Measure 1:
    CASE [Metric]
    when 'PCL' THEN [PCL]
    WHEN 'WRTO' THEN [Wrto]
    when 'RECV' then [Recoveries]
    else NULL END

    Measure 2:
    CASE [Metric]
    when 'PCL' THEN [PCL_Ratio%]
    WHEN 'WRTO' THEN [WRTO_Ratio% ]
    when 'RECV' then [Recovery_Ratio% ]
    else NULL END

    So when i put into Marks shelf ,so for title it shows no header or even if you select from measure names it display only 1 title ,so for displaying header i created another calculation.
    Header Calculation:
    case [Metric]
    WHEN "PCL" then "PCL"
    when "WRTO" then "WRTO"
    when "RECV" then "RECV"
    END

    and placed in the column shelf ,but the problem is when i select PCL from my parameter created apart from above calculations for header and metric ,it shows right values but header always shows PCL but i want to display PCL$ PCL% as they are 2 columns values but i couldn`t find any way to bifurcate single column alias into 2 column alias.

    So wondering is there any way if i select PCL then it display only
    PCL$ PCL%
    when WRTO
    WRTO$ WRTO %
    RECV
    RECV$ RECV%


    ReplyDelete