Dynamic Share Percentage Based on Hierarchy Orientation

[dakotaperryman]

Hello Experts, I have the below issue in my PBI report that I am in need of some assistance to resolve. The requirements given to us by the business unit seem very straight forward, but appear to be very complex in PBI. Please see below the requirements and an example. Feel free to ask any questions that would help get this answered. Thank you all.

Requirements:

• Using a matrix visual, create a hierarchy that will show the subtotal percentage of the selected parent. The parent and child attributes will not be known until they are input into the hierarchy. This is because the data that we have does not have set parent/child attributes.  
• It is also worth noting that we have 10+ hierarchy orientations that are known, and many more that may be unknown. So we can not hard code any of the orientation layouts, this must be completely dynamic.

Example:

• Left image can be normalized as: "Company A had 8 channels that contributed to 69 percent of the total profit. Of those channels, we can see the share percentage of each. These sub categories sum to 100% of the parents total."
• Right image is basically the inverse: "Channel 1 was made up for two profit channels; A & B. Of those two channels, A did 87% of revenue and B did 12%. These sub categories sum to 100% of the parents total."

[dakotaperryman]

Year	CompanyName	Promotional Channel	Specialty Short Name	Sum of Dollars
2019	Company_A	DTC	Specialty_1	67939157.82
2019	Company_A	ePromo	Specialty_2	681000
2019	Company_A	ePromo	Specialty_3	639000
2019	Company_A	ePromo	Specialty_4	132000
2019	Company_A	ePromo	Specialty_5	90000
2019	Company_A	Event	Specialty_3	5693640
2019	Company_A	Event	Specialty_5	998190
2019	Company_A	Sample	Specialty_2	197410.1971
2019	Company_A	Sample	Specialty_6	550195.644
2019	Company_A	Sample	Specialty_5	2815495.896
2019	Company_B	DTC	Specialty_1	569070082.6
2019	Company_B	ePromo	Specialty_7	125000
2019	Company_B	ePromo	Specialty_2	568000
2019	Company_B	ePromo	Specialty_3	3540000
2019	Company_B	ePromo	Specialty_4	1111000
2019	Company_B	ePromo	Specialty_5	332000
2019	Company_B	Event	Specialty_2	15329912
2019	Company_B	Event	Specialty_4	2291965
2019	Company_B	Event	Specialty_5	13618270
2019	Company_B	Journal	Specialty_1	4760607
2019	Company_B	Sample	Specialty_2	4015363.119
2019	Company_B	Sample	Specialty_6	6075946.901
2019	Company_B	Sample	Specialty_4	3179414.932
2019	Company_B	Sample	Specialty_5	10539749.79
2019	Company_C	ePromo	Specialty_2	276000
2019	Company_C	ePromo	Specialty_5	90000
2019	Company_C	Event	Specialty_4	1165494
2019	Company_C	Event	Specialty_5	2956546
2019	Company_C	Journal	Specialty_1	839588
2019	Company_C	Sample	Specialty_4	9109703.854
2019	Company_C	Sample	Specialty_5	4592272.89
2020	Company_A	DTC	Specialty_1	103991912
2020	Company_A	ePromo	Specialty_2	99000
2020	Company_A	ePromo	Specialty_3	583000
2020	Company_A	ePromo	Specialty_4	108000
2020	Company_A	ePromo	Specialty_5	331000
2020	Company_A	Event	Specialty_3	306843
2020	Company_A	Event	Specialty_4	4968346
2020	Company_A	Event	Specialty_5	2389557
2020	Company_A	Sample	Specialty_2	716827.8962
2020	Company_A	Sample	Specialty_6	490286.8177
2020	Company_A	Sample	Specialty_5	1803054.002
2020	Company_B	DTC	Specialty_1	501801145.9
2020	Company_B	ePromo	Specialty_2	206000
2020	Company_B	ePromo	Specialty_6	1878000
2020	Company_B	ePromo	Specialty_4	1221000
2020	Company_B	ePromo	Specialty_5	495000
2020	Company_B	Event	Specialty_2	9400229
2020	Company_B	Event	Specialty_4	3122809
2020	Company_B	Event	Specialty_5	4264413
2020	Company_B	Journal	Specialty_1	2426714.5
2020	Company_B	Sample	Specialty_2	7655083.042
2020	Company_B	Sample	Specialty_6	1227377.427
2020	Company_B	Sample	Specialty_5	5856243.951
2020	Company_C	DTC	Specialty_1	621000
2020	Company_C	ePromo	Specialty_2	305000
2020	Company_C	ePromo	Specialty_6	935000
2020	Company_C	ePromo	Specialty_5	115000
2020	Company_C	Event	Specialty_4	119653
2020	Company_C	Event	Specialty_5	3175197
2020	Company_C	Journal	Specialty_1	1665235.1
2021	Company_A	DTC	Specialty_1	59853083.4
2021	Company_A	ePromo	Specialty_3	230000
2021	Company_A	ePromo	Specialty_4	371000
2021	Company_A	ePromo	Specialty_5	67000
2021	Company_A	Event	Specialty_5	446205
2021	Company_A	Sample	Specialty_5	1498953.013
2021	Company_B	DTC	Specialty_1	161882701.3
2021	Company_B	ePromo	Specialty_2	225000
2021	Company_B	ePromo	Specialty_6	4174000
2021	Company_B	ePromo	Specialty_3	282000
2021	Company_B	ePromo	Specialty_4	444000
2021	Company_B	ePromo	Specialty_5	17000
2021	Company_B	Event	Specialty_2	387588
2021	Company_B	Event	Specialty_4	1277002
2021	Company_B	Journal	Specialty_1	743896.25
2021	Company_B	Sample	Specialty_2	976907.9627
2021	Company_B	Sample	Specialty_6	6214636.555
2021	Company_B	Sample	Specialty_5	21724937.82
2021	Company_C	DTC	Specialty_1	1066055.72
2021	Company_C	ePromo	Specialty_2	89000
2021	Company_C	ePromo	Specialty_6	853000
2021	Company_C	ePromo	Specialty_4	98000
2021	Company_C	Journal	Specialty_1	524470
2021	Company_C	Sample	Specialty_6	39974041.59

[dakotaperryman]

Hello @lbendlin , thank you for your continued support on this. I posted some sample data above, and have created some of the pivots that we would expect to be able to create from this data. The screenshot below shows these pivots that have the % of the parent row as we would expect. Please let me know if you have any further questions. Thank you!

[lbendlin]

If there are no other filters involved then you can create calculated columns for the level percentages.

If you neerd to use measures then you can use ISINSCOPE to influence the measure calculation depending on which level you are looking at.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.