4 Replies Latest reply on Apr 5, 2018 2:42 PM by Matt Day

# Percentage of total, among a subset of data

Hi all

I'm not sure if this is a problem that I can solve with LODs, but I haven't been able to figure out a way around it yet.

I have a data set with Region, Account size and Pack type along with sales this year and prior year.

I'm looking to understand how combinations of these things have performed - Region/Account size, Account size/Pack, Region/Pack

Step 1

I only want to take the combinations which have a NET positive value, and work out what % this net value has of the total positive value.

For instance, in the example below the net of North/Small is +1, the net of East/Small is -4, the net of East/Medium is -2 and the net of North/Medium is +5

I would want the calculation to return North/Small as 17% (i.e. 1/6) of total positive group sales

 Region Account Size Pack Type Sales Sales YA Difference (Excel Calc) Region/Size Total (Excel Calc) North Medium 1pk 24 23 +1 Positive North Medium 2pk 16 12 +4 Positive North Small 1pk 64 59 +5 Positive North Small 2pk 73 77 -4 Positive East Small 1pk 52 50 +2 Negative East Small 2pk 30 36 -6 Negative East Medium 1pk 40 38 +2 Negative East Medium 2pk 44 48 -4 Negative

In Excel I can get there using this Region/Size Total "helper" field, and I think that's my biggest problem in Tableau.

Step 2

I'd like to be able to compare this percentage with what was expected based on Sales YA.  Using the same example, North/Small accounted for 77+59 sales last year and North/Medium accounted for 23+12.  So last year, North/Small was 80% of the sales of the groupings that grew this year.

So comparison to expectations for North/Small would be 17-80 = -63

Comparison to expectations for North/Medium in the same vein would be 83-20 = +63

The methodology to get there is presumably very similar to step 1, but the solution needs to be able to combine the two calculations, which can be a problem when it comes to aggregating data.

Step 3 (if possible)

In an ideal world, I'd love to be able to create a single table which compares across these groupings, which would presumably be created as separate worksheets.  In other words an ability to show whether "Large size, 1pk" had higher sales vs expectations than "North region, Medium size".  If not, I'm happy with a solution which just takes the top 3 of each combination and I can create a dashboard which combines them.

The final data set will be extremely large for this, so hopefully something without excessive processing requirements, although I realize that's asking a lot!

Thanks!

Matt

Packaged workbook attached

• ###### 1. Re: Percentage of total, among a subset of data

Hi Matt,

Here it is. Pl follow each Calculation I did, I created one single table for Clarity. If it Helps, Pl mark it Helpful and CORRECT to close Thread.

Thanks

Deepak 1 of 1 people found this helpful
• ###### 2. Re: Percentage of total, among a subset of data

Hi Deepak

Thanks for the quick response - this is a lot further than I would have been able to get, but while it appears to give the right answer at first glance, I don't think it completely solves the problem.  In your "Calculation2" field you've used the expression

{FIXED Region:SUM(IF [Calculation1]>0 THEN [Calculation1] ELSE 0 END)}

This sums up all of the positive combination entries within each region, but not within the entire data set.

That worked in this case because in the sample data "North" was the only region which had a combination (both North/Medium and North/Small) with a positive net contribution, but that wont always be the case.

In case the original question wasn't clear, I would need the percentage of positive contribution that a Region/Account (or Account/Pack etc) makes to the TOTAL positive contribution, across all regions.

Is there a way to create this calculation that you have above without using Region as the "Fixed" parameter?  Is there an "All" notation or something which can be used instead?

Thanks!

• ###### 3. Re: Percentage of total, among a subset of data
1 of 1 people found this helpful
• ###### 4. Re: Percentage of total, among a subset of data

Deepak - thank you, this works perfectly!