Qlik Sense Pivot % of Parent Row Total (Similar to Excel Pivot)

[mhill176]

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?

[Kushal_Chawda]

@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))

[mhill176]

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.

[mhill176]

Still looking for a solution.