6 Replies Latest reply on Dec 6, 2018 11:25 AM by androo

# Different Aggregation Types

I have 100 records in a data set, and these records will fall into one of 3 buckets (A, B, C).

I would like to display the data in a worksheet (looking for a worksheet solution, not a dashboard) so that beneath each bucket a percentage to total records is shown (as a percent), but for the grand total, I would like to see 100 (the total number of records).

EXAMPLE:

A          B          C          Grand Total

Project          25%     40%     35%          100

What is the best way to do this?

Thanks.

• ###### 1. Re: Different Aggregation Types

Create 2 sheets with % and Grand Total.

Drag  both to the dashboard side by side

Thanks,

Ritesh

• ###### 2. Re: Different Aggregation Types

Thanks, Ritesh. I am aware of the dashboard solution. I am hoping to find a solution strictly within the worksheet itself, if possible.

I tried some SQL manipulation, but I am not sure how to split the logic using Tableau's calculation language.

• ###### 3. Re: Different Aggregation Types

This is tricky...There may be a better solution than this, but I'll give it a shot. You could create two calculated fields--one that shows a % if it's not a grand total, otherwise shows NULL. And one that shows a SUM if it is a grand total, but otherwise shows a NULL.

Not Grand Total

// If only one segment, then assume this is a regular total...

// ...for a single state and segment, so calculate % of sales.

IF COUNTD([Segment])=1 THEN

// Single segments, so assume note a grand total.

SUM([Sales])/SUM({FIXED [State]: SUM([Sales])})

END

Grand Total

// If has multiple segments, then assume this is a...

// Grand total, so just display total sales.

IF COUNTD([Segment])>1 THEN

// Multiple segments, so assume a grand total.

SUM([Sales])

END

Then you can drag those both to the Text card.

Unfortunately, this falls apart if you have one item that has 100% of it's data in only one of the categories as shown in Wyoming above.

It certainly feels like there should be a better solution for this.

• ###### 4. Re: Different Aggregation Types

Hi Androo,

Here's an approach (attached 2018.3 workbook using Superstore data).  Tableau is fairly rigid with tabular data so this is as good as I could get it for you; if you can live with totals to left side, this might be an idea:

• ###### 5. Re: Different Aggregation Types

Cool concept. I will try to see if it works in my situation. I will likely have buckets with 0 values from time-to-time, but this may be fine.

I also thought this was something that should have a simple solution.

Thanks, Ken!

• ###### 6. Re: Different Aggregation Types

I think I have something...

I use a UNION ALL concept where I union the data set to itself and assign a [flag] (Source/Copy) to designate the original from the duplicate records.

Here's the formula I use to achieve the split I desire (still have to work with formatting):

IF ATTR([flag])="Copy" THEN

-WINDOW_SUM(SUM([Number of Records]), 0, IIF(FIRST()==0, LAST(), 0))

ELSE

SUM([Number of Records]) / SUM({ FIXED [flag]="Copy" : SUM([Number of Records]) })

END

Essentially, the Copy records are used to generate the total, while the Source records are used for the category breakouts.

I created a custom header to accommodate this using:

IF [flag]="Source" THEN STR([StandardHeader]) ELSE "Total" END

Notes:

In my application, I have additional items that may be included in the LOD denominator formula, but this is the simplified concept.

I still need to try the other suggestions to see if they also solve the problem.

Formatting (edited):

I added a * (-1) to the "Copy" section of the formula above (shown in red).

Having the data on different sides of zero, I use the following custom number format to print the data in percentages and integers: #,##0%;#,##0

Also, because the values are on opposite sides of 0, I can use a 2-Stepped Custom Diverging Palette to display the Total as a darker (or different) hue than the buckets by setting the center as 0.

Thanks!