6 Replies Latest reply on Oct 7, 2016 11:10 AM by victor chan

# Level of Detail Chart ends up like Stacked Bar

So I'm trying to create a chart that looks like this:

The problem is that my dataset has a row for each quarter and I only want to pick the latest one. For each employee id they can start or end at any moment. Because of this I use a Level of Detail calculation so that I can grab the maximum row. On top of that creating a ratio and then plotting it as a percentage isn't working for me so I had to create a percentage bucket.

In this scenario, I want to see the distribution of my employees in how well they close deals (as a percentage). So I have two columns, one of the total opportunity in dollars of how many deals they could have potentially closed and how how much in dollars they actually closed. Ultimately, this is what I am getting. Ideally count wouldn't stack and I could hover over the bar like it is one group. I use a LoD calculation to find the max quarter that an employee is with us and then create a filter to either hide of show the latest quarter.

I suspect I am unecessarily using too many LoD to get this right but I can't get around it.

Any help or even a link to a previous post would be greatly appreciated.

The sample dataset and the sample Tableau workbook are attached.

Thank you.

• ###### 1. Re: Level of Detail Chart ends up like Stacked Bar

I've had a stab at this, I'll start with the result so you'll know if it's what you expected.

Next the logic:

1. I created a field to get the MaxDate for each employee: { FIXED [Employee id]:MAX([Quarter])}

2. I created a field to act as a filter using that field: [Quarter] = [MaxDate]

3. Set the filter field to True in the filter shelf

4. Next to create the % field. I want this to be a dimension so am using a FIXED formula: { FIXED [Employee id]: SUM([Closed Deals])/SUM([Deal Opportunity])}

5. Drag the % field to Dimensions to make it a dimension

6. Now we have all of the required components, next to bring it all into the view. Drag % to Columns and do a Count of Employee ID on Rows.

7. This still won't look quite right...make the % field Continuous in the Columns shelf

8. Add the DateFilter field to Context

If that solution is what you're looking for all that is left is to format appropriately.

1 of 1 people found this helpful
• ###### 2. Re: Level of Detail Chart ends up like Stacked Bar

Thank you very much Andrew.

I stupidly bucketed the percentage values in a calculated measure instead of a dimension and making it continuous!

Definitely one to remember.

Thanks again,

Victor.

• ###### 3. Re: Level of Detail Chart ends up like Stacked Bar

Nothing stupid at all about what you were doing Victor, the request you had is actually quite complex. It's taken me 7 years of using Tableau to be able to answer that question in 15 mins - and even then it takes a lot of thought. There are a number of lesser known techniques incorporated to solve this.

• ###### 4. Re: Level of Detail Chart ends up like Stacked Bar

Never feel silly for trying to learn man.

• ###### 5. Re: Level of Detail Chart ends up like Stacked Bar

They can put the end result?

• ###### 6. Re: Level of Detail Chart ends up like Stacked Bar

yea, I uploaded the version "_solved" at the top. So this is the step 4 in Andrew's answer. The two tabs "before" and "after" detail the difference.