8 Replies Latest reply on Jan 25, 2017 9:23 AM by Steve Schwantes

# creating dual axis line and bar chart

I have attached a packaged workbook.with data that has been anonymized.  I am trying to create a dual axis chart with Industry description tied to left axis in bar chart format, with activity outcome on right axis.

My activity outcome field is a calculated field based on activity outcome field in underlying dataset.  Underlying field has 13 categories which my calc'd field groups down to 3:  dead, unqualified and qualified.

I want to filter out unqualified and dead, and just show qualified as a % of total records (not just as % of qualified) across each industry type.  So, essentially the 881 qualified rows or 6% of total records, across each industry type (for the whole record set of 15k rows).  So, the 6% would tally up across each industry bar to 881 (as %).

Not sure this is possible w/o changing structure of the underlying data.  It should resemble this chart made in Excel:

thanks!

• ###### 1. Re: creating dual axis line and bar chart

Try creating the following field:

[Percent Qualified by Industry] : SUM(IF [Calculation1] = 'Qualified' THEN 1 ELSE 0 END)/SUM([Number of Records])

Use that instead of your table calculation and remove all filters. With a bit of clean-up, you get the following chart

• ###### 2. Re: creating dual axis line and bar chart

Thanks Stephen!

Looks like you have proven it can be done entirely w/in Tableau w/o modifying underlying data.  Awesome.

Let me see if I can replicate.

Steve S.

• ###### 3. Re: creating dual axis line and bar chart

HI Steve,

I hope that i did understand your challenge correct. Find my approach as reference below and stored in attached workbook version 10.0

• ###### 4. Re: creating dual axis line and bar chart

Sorry, I don't understand the formula you posted.  can you elaborate?

thanks,

Steve

• ###### 5. Re: creating dual axis line and bar chart

Sure, I can walk you through the formula.

What you seem to be looking for is the percentage of qualified records by industry - that is, the number of qualified records for a given industry divided by the total number of records for that industry. Whether a record is qualified or not is determined by your [Calculation1] field. Because your chart is breaking on [Industry], the statement SUM(IF [Calculation1] = "Qualified" THEN 1 ELSE 0 END) will look at each record for a given industry, then if that record is listed as "Qualified" the sum will add 1 to the value. This will give you the total number of qualified records for that industry. The SUM([Number of Records]) expression in the denominator will count all records associated with that industry, whether they are qualified or not. Dividing one by the other gives you what you were looking for.

See the attached workbook if you would like to see it implemented.

• ###### 6. Re: creating dual axis line and bar chart

This is where it's at now after emulating the new calculated field as calc field2:

With this being the calc for cacl2 field

SUM(IF [Calculation1] = 'Qualified' THEN 1 ELSE 0 END)/SUM([Number of Records])

• ###### 7. Re: creating dual axis line and bar chart

thanks again for the walk thru - understand now and have implemented that much so think I'm now very close but when comparing your copy vs my original copy (w/ emulated calc field 2) .. My qualified % by industry is still tying to the total count by industry ... not sure what I have yet to do to fix this piece of clean up work ... the charts should look the same as far as I can tell ... new copy of packaged wkbk attached.

thanks,

Steve

• ###### 8. Re: creating dual axis line and bar chart

Okay, think I've got it cleaned up ... thanks again ... !!!

For some reason, it was still showing the counts by industry even though the line chart was correctly tied to the right axis ... just had to adjust some formatting ... so now it looks like this .. .