
1. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Carl Slifer Mar 18, 2016 4:01 AM (in response to Lucy Jones)Hi Lucy,
Attached you will find a crosstab and a barchart using your data in the excel snippet. The trick to this is to use measure names and measure values: For more reading on these fun toys that Tableau provides: http://interworks.co.uk/blog/measurenamesvaluestableau/
Cheers!

2. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Lucy Jones Mar 18, 2016 4:26 AM (in response to Carl Slifer)Hi Carl,
This is great, thank you very much. I have another issue though and it is completely my fault, I should have mentioned it before so I apologise. The data I send is just a snapshot of data after it has been manipulated for the graph. We have a huge amount in tableau. The raw data is set up as per the attached so the approved/pipeline are actually a dimension not a measure. The % of target and gap to target are calculations that we have created in tableau so they do come under measures but I am not sure how I can include the Approved/Pipeline split as it is a dimension. (I am not sure if its important but there are 2 data sources, one has the sales info and the second is the target information so the calc fieleds etc use both data sources). If you can help with this you would be a lifesaver. Thank you again and apologies for not providing the raw data at the beginning.
Lucy

Example Data.PNG 15.7 KB


3. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Carl Slifer Mar 18, 2016 5:09 AM (in response to Lucy Jones)Hi Lucy,
Here we go. This has a blend as well and a few calculated fields which term a dimension, measure pair into just a measure for each.
Cheers!

BlendedStackedBar.twbx 34.8 KB


4. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Lucy Jones Mar 18, 2016 6:15 AM (in response to Carl Slifer)Hi Carl,
Fantastic, thank you :)
One last problem, if the sales for a country exceed the target then the gap to target shows as a minus (see attached), is it possible to maybe change the calculated field for gap % to say that if the result is a minus to put it at 0 or to not include it at all?
Thank you so much
Lucy

Example.PNG 23.1 KB


5. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Carl Slifer Mar 18, 2016 6:45 AM (in response to Lucy Jones)Lucy,
It already does. No changes need to be made. I built this into it. There's the same workbook but with another country added in where the target was exceeded.
Cheers!

BlendedStackedBar.twbx 37.5 KB


6. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Lucy Jones Mar 18, 2016 8:38 AM (in response to Carl Slifer)
Hi Carl,Thank you so much for all your help with this.
Is there a way that when the country over exceeds target we dont show the gap %? I don't really want to show the negative as it may confuse the audience. I know I keep asking questions and I apologise but if we can show the gap% only when there is a gap then that would be amazing.
Thank you
Lucy

7. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Carl Slifer Mar 18, 2016 9:31 AM (in response to Lucy Jones)Lucy,
Change the Gap % Calculation to:
IF SUM([Sales]) >= SUM([Target (BlendingExample) (2)].[Target]) THEN 0
ELSE [Gap] / SUM([Target (BlendingExample) (2)].[Target]) END
This does what you've asked. Sometimes when you ask yourself the question out loud its all you need to do. You had it close just with the question. If the gap % is a negative number can we now show it?
Here's how we break that down. What is Gap %, well its derived from the difference between the SUM of Sales and the Target. And we want to keep it when Target exceeds SUM. Therefore when Sum exceeds target we want to remove it.
So we already asked ourselves the question IF X condition is met then Don't show it, ELSE do show it.
The way we don't show a value is to either make it a NULL or to treat it as a 0. In this case we will treat it as 0 so that it is easier to understand..
IF X condition is met make it 0 ELSE use the real value.
And that's what our calculation is doing.
Cheers!

8. Re: Please help  I need to produce a bar graph to show the percentage of approved sales, pipeline sales and gap to target, the sales are by market and each market has a different target
Lucy Jones Mar 18, 2016 9:38 AM (in response to Carl Slifer)Hi Carl,
This is fantastic, thank you so much for all your time today, I really appreciate it.
Have a great weekend.
Lucy