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

    Percentage of total, among a subset of data

    Matt Day

      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

       

        

      RegionAccount SizePack TypeSalesSales YADifference (Excel Calc)Region/Size Total (Excel Calc)
      NorthMedium1pk2423+1Positive
      NorthMedium2pk1612+4Positive
      NorthSmall1pk6459+5Positive
      NorthSmall2pk7377-4Positive
      EastSmall1pk5250+2Negative
      EastSmall2pk3036-6Negative
      EastMedium1pk4038+2Negative
      EastMedium2pk4448-4Negative

       

      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