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
What if I want to have several measures go into the rows field? Case when "Web" then measure 1, measure 2, measure 3, etc?
ReplyDeleteAll you would need to do is create a new calculated field for each measure. You can link them all to the same parameter.
DeleteFor 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
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.
DeleteI 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
DeleteHi 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!
DeleteHi 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!
Deletesort is not working when you swap the measure
ReplyDeleteThanks for your reply!!! Could you be more specific? Perhaps sending me a mock-up and/or .twbx?
Deletethanks for your replay. Let me explain here.
ReplyDelete1.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.
pls check this.
ReplyDeletehttp://community.tableausoftware.com/thread/127344
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?
ReplyDeleteI think I understand what you were asking. I "cheated" by using a text box on a dashboard. You can download it here.
Deletehttp://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
I cannot open this file. Could you please write the steps that you follow?
DeleteTry this link. It was made in Tableau 7.
Deletehttp://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
Thanks for your reply! it will be useful for me
Deletethank you for your post......
ReplyDeleteBut 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
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?
DeleteHi 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!
ReplyDeleteTo 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.
DeleteThanks 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?
Deletehttp://community.tableausoftware.com/welcome
DeleteThanks 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 :
Deletehttp://community.tableausoftware.com/message/238576?et=watches.email.thread#238576
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!
DeleteHi Brad,
ReplyDeleteI 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?
Kuber Nagpal linked this forum post which has a possible workaround.
Deletehttp://community.tableausoftware.com/message/238576?et=watches.email.thread#238576
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
ReplyDeleteproduct 1 5%
60%
30%
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.
Deletehttp://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?
Hi Brad,
ReplyDeleteMy 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....
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.
DeleteHi Brad
ReplyDeleteThis 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
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?
DeleteI 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?
ReplyDeleteThanks 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?
DeleteThanks!
Hi Brad ,
ReplyDeleteI 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 !!!!
Atts123,
DeleteThanks 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
Hi Brad, i was hoping to open the link you posted on June 24th, 2013.
ReplyDelete"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.
Steve,
DeleteThanks so much for commenting! Unfortunately, these temporary sharing sites don't store stuff for very long. Could you explain the issue you are having?
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.
ReplyDeleteUnfortunately, 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!
DeleteDo you have any other suggestion for filtering for certain headers?
ReplyDeleteSteve,
DeleteHere's a thread from the Tableau forums that has a possible, yet clunky workaround.
http://community.tableausoftware.com/thread/110424
Thank you Brad, this seems like it will work well actually for what i need, probably better actually.
ReplyDeleteOne question though, i'm just trying to figure out how the Parameter is controlling which sheet is in view on the dashboard?
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.
ReplyDeleteThere are a couple of ways. The simplest is that you could create a calculated field that says
DeleteIF [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?
Thanks for your help!
ReplyDeleteHow do I dynamically change the name of the column header that is selected using the parameter?
ReplyDeleteHi,
ReplyDeleteI 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
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.
ReplyDeleteFor 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
I want to select multiple dimensions dynamically. How to do this because in parameter we can select only one value at a time?
ReplyDeleteThanks,
Ashish Gupta
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!
ReplyDeleteThanks Brad for this post.
ReplyDeleteI 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?
when we are labeling profit and quantity ,we need to show profit in currency and quantity in normal number format, is that possible
ReplyDeleteHi, 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?
ReplyDeleteThank you, Nofar
Hi,
ReplyDeleteI'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
Hi,
ReplyDeleteI 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
Hi Brad,
ReplyDeleteHow can we include 'All' option to the parameter so that on selecting 'All',one can display all the measures??
Hi Brad,
ReplyDeleteIn case i want to select more than one field say sales and profit both, how can we achieve that?
Thanks
Thaaanks, very useful!! Cheers from Peru
ReplyDeleteIs 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.
ReplyDeleteHi Brad ,I have a tableau issue:
ReplyDeleteFor 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%