1 of 1 people found this helpful
Using a Table Calculation filter will allow you to filter without impact the percentage calculation--here's an example:
Well, that definitely helps. Now I have something that looks like this (I've blocked out the country names, and the business entity names).
Can I group the business entity names, so that I just have one percentage? I do have the Business Entity as a set but I can't do the calculation on the set because it says it's being called with a boolean. I can show the total bookings as a subtotal of the country, but I want that side by side (I removed it).
I'm really struggling with this, and I shouldn't be. I'm sure it's something simple, and I'm still tinkering.... any other guidance would be helpful. Like I said - the goal is the format above, and I can't quite get it there with the "Jedi" trick suggested.
I'm not sure what you are looking for, in terms of an end result. If you can post a sample packaged workbook, along with a mock up of your intended output, I'll be happy to take a look. You can use a sample dataset provided with Tableau to create a similar view, or pull a subset of your records into Excel, randomize them, and pull that into Tableau as an extract for us to use within the thread. I'd guess that once you post a workbook, you'll have a solution very quickly.
What I want is a layout just like what I posted in the original message. The column for "Specific Product Bookings" are going to be all the same product, because I'm analyzing a subset of a large data set. I can't really get a packaged workbook together, because I'm pulling from a live database, but I'll try to mock something together in Excel as a datasource that I can post here.
Like I said, you can copy and paste a subset of records into Excel (or create a very simple data set that matches the structure and granularity of your actual data), pull that into Tableau as an extract, and post the work you've been able to complete here, along with a mock up of what you want for an end result (including the values you want displayed, etc). Alternatively, you can try to mock up a similar example using the Superstore Sales dataset provided with Tableau. Just make sure the granularity of the data and the end goal are similar.
Its very difficult to help without this, as we cannot see the data or calculations--and I'm still not clear on your end goal, based on the original table and the screenshot you posted; mostly, because the first table does not include business entities. This is where most of my confusion sprouts from.
Once you get some sample data together, and can explain the goal clearly (no offense--I just don't understand the end goal here), I'm certain a solution can be applied.
I originally thought the goal was to filter without impacting the percentage results--the Table Calc filter method allows us to do this, but perhaps I misunderstood what you are wanting to do.
Hi Ann! As I understand your question, you want % of bookings by country, not broken down by business entity. Is there a reason business entity is in your view? Are you trying to get an average % of bookings at a business entity level of detail? If so, there could be ways to build a calculated field to get what you need, depending on how your original data is structured.
Ok, I've attached a workbook above with mock data.
Here's what I need to see as the end result:
Country (Name of the country) | % of Total Bookings for the chosen Business Entities (the calculation is basically the sum of the bookings for the chosen business entities over the total bookings for that country) | $ of Bookings for the chosen Business Entities (the sum of the bookings for the chosen business entities)
Here's the rub: I need those Business Entities to be grouped as one instead of individual items. Heck, I don't even need to see them on the dashboard at all. I've included the subtotals ONLY for illustrative purposes that I've got the percentage calculations down, but I can't find the way to that last mile where I can remove the entity and have the calculation stand. The minute I remove it, the discrete sum(actual bookings) fails. If I hide the header, the data stays, but they're still on individual lines (not what I want)
On my final dashboard I have filters that drive quarter, Business Entity (there are two we're focusing on, and we want to have users show one, the other, or both), country name, and Business Sub Entity (BSE).
This item would likely be affected by all of these, but I can probably drop BSE if I need to.
Is that clearer?
Yes, somewhat. It would be helpful to know what you are expecting as an end result, including what values you expect to see, and at what level of detail, but I'm not sure if you know what values you are expecting. I assume you are wanting the combination of the two business entity percentages at the country level? So, for country 6, you'd want to see 14.26%? What do you want to see for the Actual Bookings, then? For Country 6, would it be the sum of 43, 403 and 79,227 or the $860,144 value that is showing for your Total?
This is what I meant by a mock up of your intended results--it helps to know what the expected output should be.
Yes, for Country 6, I would want to see the 14.26% (the combined bookings for the two entities divided by total bookings for that country), and I would want to see the total bookings value (the 860,144).
I'm not sure why this is so difficult, but if we could aggregate the percent of total like we can the Actual Bookings, we could easily arrive at a solution. I just cannot seem to find a way to total the percentages at the country level; no matter what I try to aggregate the percentage values, I get 100% for each country.
I tried it out and, for some reason, Country 101 is throwing off all the numbers. I am not able to figure out why. I was able to get this once I filtered out Country 101.
The last column's calculation is SUM([Actual Bookings])/TOTAL(SUM([Actual Bookings])). The numbers aren't right since Country 101 is not included, but hopefully this helps as a start!
Yeah, I don't believe that is accurate either. Country 101 shouldn't have any impact on this calculation, as far as I can tell. Plus, the business entities do need to be considered.
I know. I'm just trying to provide it to move the discussion forward.
You might think about trying one of these:
- Nested table calculations (change the existing one from a quick table calc to a custom one where you can then embed it in another), specifically you might think of a running total.
- Data blending using a copy of the data source is another way to get aggregates that are not changed by filters. That would avoid some of the table calc complexities -- you could get the % with an aggregate level calculation and then use a much simpler table calc to WINDOW_SUM those.
Hope that helps!