Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've built a pivot table that allows the user to select dimensions (rows) from a pool of fields like Manufacturer, Brand, Segment, etc.
Multiple fields can be added at once to allow for deeper dives into the Volume for each selection
Example:
User selects Manufacturer and Brand, Volume is selected automatically as the viewed Metric.
202101 | 202102 | 202103 | |
Manufacturer A | 30 | 36 | 33 |
Brand 1 | 19 | 17 | 9 |
Segment X | 14 | 10 | 7 |
Segment Y | 5 | 7 | 2 |
Brand 2 | 11 | 19 | 27 |
Segment X | 7 | 9 | 18 |
Segment Y | 4 | 10 | 9 |
Manufacturer B | 42 | 37 | 40 |
Brand 3 | 16 | 17 | 10 |
Segment X | 7 | 11 | 5 |
Segment Y | 9 | 6 | 5 |
Brand 4 | 18 | 9 | 15 |
Segment X | 12 | 5 | 9 |
Segment Y | 6 | 4 | 6 |
Brand 5 | 8 | 11 | 25 |
Segment X | 1 | 8 | 17 |
Segment Y | 7 | 3 | 8 |
I would like to display a percentage of Segment X and Y that add up to 100% and the Brand should also total to 100% of Manufacturer. The Manufacturers should also add up to 100%.
So For this example:
202101 | % | |
Manufacturer A | 30 | 41.7% |
Brand 1 | 19 | 63.3% |
Segment X | 14 | 73.7% |
Segment Y | 5 | 26.3% |
Brand 2 | 11 | 36.7% |
Segment X | 7 | 63.6% |
Segment Y | 4 | 36.4% |
Manufacturer B | 42 | 58.3% |
Brand 3 | 16 | 37.3% |
Segment X | 7 | 43.8% |
Segment Y | 9 | 56.2% |
Brand 4 | 18 | 42.9% |
Segment X | 12 | 66.7% |
Segment Y | 6 | 33.3% |
Brand 5 | 8 | 19.4% |
Segment X | 1 | 12.5% |
Segment Y | 7 | 87.5% |
If the user chooses to remove Segment or Brand or Manufacturer, it should recalculate to 100% automatically based on the parent row in every case.
Can anybody see a solution for this?
@mhill176 if your measure is sum(Value) , you could try below
=Pick(Dimensionality(),
sum(Value)/sum(TOTAL Value),
sum(Value)/sum(TOTAL <Manufacturer> Value),
sum(Value)/sum(TOTAL <Brand,Manufacturer> Value))
I tried this and the percentage does not aggregate correctly - there are more rows to select from than just Manufacturer/Brand/Segment/Sub-Segment/Item, etc. so I need the solution to be completely agnostic to however many rows are added to the pivot.
So if I user decides to add
Segment
Item
The item lines will always add up to 100% of Segment and all Segments will add up to 100%.
If the user selected
Market Area
Region
Manufacturer
The manufacturer will add up to 100% of Region, which will add up to 100% of all Market Areas, which will sum up to 100%. The solution must be dynamic to allow for all rows to be added and aggregated.
Still looking for a solution.