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
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)|
In Excel I can get there using this Region/Size Total "helper" field, and I think that's my biggest problem in Tableau.
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!
Packaged workbook attached
SimpleDataset.twbx 8.0 KB