topic Dynamic Share Percentage Based on Hierarchy Orientation in Desktop
https://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2160778#M794990
<P>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.</P><P> </P><P><STRONG>Requirements:</STRONG></P><UL><LI>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. </LI><LI>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.</LI></UL><P> </P><P><STRONG>Example:</STRONG></P><UL><LI>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."</LI><LI>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."</LI></UL><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dakotaperryman_0-1635423149657.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/621306i7DC22E44E6ADB1ED/image-size/medium?v=v2&px=400" role="button" title="dakotaperryman_0-1635423149657.png" alt="dakotaperryman_0-1635423149657.png" /></span></P>Thu, 28 Oct 2021 12:14:32 GMTdakotaperryman2021-10-28T12:14:32ZDynamic Share Percentage Based on Hierarchy Orientation
https://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2160778#M794990
<P>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.</P><P> </P><P><STRONG>Requirements:</STRONG></P><UL><LI>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. </LI><LI>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.</LI></UL><P> </P><P><STRONG>Example:</STRONG></P><UL><LI>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."</LI><LI>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."</LI></UL><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dakotaperryman_0-1635423149657.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/621306i7DC22E44E6ADB1ED/image-size/medium?v=v2&px=400" role="button" title="dakotaperryman_0-1635423149657.png" alt="dakotaperryman_0-1635423149657.png" /></span></P>Thu, 28 Oct 2021 12:14:32 GMThttps://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2160778#M794990dakotaperryman2021-10-28T12:14:32ZRe: Dynamic Share Percentage Based on Hierarchy Orientation
https://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2164480#M795859
<P>If there are no other filters involved then you can create calculated columns for the level percentages.</P>
<P> </P>
<P>If you neerd to use measures then you can use ISINSCOPE to influence the measure calculation depending on which level you are looking at.</P>
<P> </P>
<P>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. </P>Sat, 30 Oct 2021 18:01:52 GMThttps://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2164480#M795859lbendlin2021-10-30T18:01:52ZRe: Dynamic Share Percentage Based on Hierarchy Orientation
https://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2166545#M796367
<TABLE><TBODY><TR><TD>Year</TD><TD>CompanyName</TD><TD>Promotional Channel</TD><TD>Specialty Short Name</TD><TD>Sum of Dollars</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>67939157.82</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>681000</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_3</TD><TD>639000</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>132000</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>90000</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>Event</TD><TD>Specialty_3</TD><TD>5693640</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>Event</TD><TD>Specialty_5</TD><TD>998190</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_2</TD><TD>197410.1971</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_6</TD><TD>550195.644</TD></TR><TR><TD>2019</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>2815495.896</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>569070082.6</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_7</TD><TD>125000</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>568000</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_3</TD><TD>3540000</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>1111000</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>332000</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_2</TD><TD>15329912</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_4</TD><TD>2291965</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_5</TD><TD>13618270</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Journal</TD><TD>Specialty_1</TD><TD>4760607</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_2</TD><TD>4015363.119</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_6</TD><TD>6075946.901</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_4</TD><TD>3179414.932</TD></TR><TR><TD>2019</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>10539749.79</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>276000</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>90000</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>Event</TD><TD>Specialty_4</TD><TD>1165494</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>Event</TD><TD>Specialty_5</TD><TD>2956546</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>Journal</TD><TD>Specialty_1</TD><TD>839588</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>Sample</TD><TD>Specialty_4</TD><TD>9109703.854</TD></TR><TR><TD>2019</TD><TD>Company_C</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>4592272.89</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>103991912</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>99000</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_3</TD><TD>583000</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>108000</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>331000</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>Event</TD><TD>Specialty_3</TD><TD>306843</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>Event</TD><TD>Specialty_4</TD><TD>4968346</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>Event</TD><TD>Specialty_5</TD><TD>2389557</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_2</TD><TD>716827.8962</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_6</TD><TD>490286.8177</TD></TR><TR><TD>2020</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>1803054.002</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>501801145.9</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>206000</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_6</TD><TD>1878000</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>1221000</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>495000</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_2</TD><TD>9400229</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_4</TD><TD>3122809</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_5</TD><TD>4264413</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Journal</TD><TD>Specialty_1</TD><TD>2426714.5</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_2</TD><TD>7655083.042</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_6</TD><TD>1227377.427</TD></TR><TR><TD>2020</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>5856243.951</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>621000</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>305000</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_6</TD><TD>935000</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>115000</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>Event</TD><TD>Specialty_4</TD><TD>119653</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>Event</TD><TD>Specialty_5</TD><TD>3175197</TD></TR><TR><TD>2020</TD><TD>Company_C</TD><TD>Journal</TD><TD>Specialty_1</TD><TD>1665235.1</TD></TR><TR><TD>2021</TD><TD>Company_A</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>59853083.4</TD></TR><TR><TD>2021</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_3</TD><TD>230000</TD></TR><TR><TD>2021</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>371000</TD></TR><TR><TD>2021</TD><TD>Company_A</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>67000</TD></TR><TR><TD>2021</TD><TD>Company_A</TD><TD>Event</TD><TD>Specialty_5</TD><TD>446205</TD></TR><TR><TD>2021</TD><TD>Company_A</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>1498953.013</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>161882701.3</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>225000</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_6</TD><TD>4174000</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_3</TD><TD>282000</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>444000</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>ePromo</TD><TD>Specialty_5</TD><TD>17000</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_2</TD><TD>387588</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>Event</TD><TD>Specialty_4</TD><TD>1277002</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>Journal</TD><TD>Specialty_1</TD><TD>743896.25</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_2</TD><TD>976907.9627</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_6</TD><TD>6214636.555</TD></TR><TR><TD>2021</TD><TD>Company_B</TD><TD>Sample</TD><TD>Specialty_5</TD><TD>21724937.82</TD></TR><TR><TD>2021</TD><TD>Company_C</TD><TD>DTC </TD><TD>Specialty_1</TD><TD>1066055.72</TD></TR><TR><TD>2021</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_2</TD><TD>89000</TD></TR><TR><TD>2021</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_6</TD><TD>853000</TD></TR><TR><TD>2021</TD><TD>Company_C</TD><TD>ePromo</TD><TD>Specialty_4</TD><TD>98000</TD></TR><TR><TD>2021</TD><TD>Company_C</TD><TD>Journal</TD><TD>Specialty_1</TD><TD>524470</TD></TR><TR><TD>2021</TD><TD>Company_C</TD><TD>Sample</TD><TD>Specialty_6</TD><TD>39974041.59</TD></TR></TBODY></TABLE>Mon, 01 Nov 2021 15:06:27 GMThttps://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2166545#M796367dakotaperryman2021-11-01T15:06:27ZRe: Dynamic Share Percentage Based on Hierarchy Orientation
https://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2166549#M796368
<P>Hello <LI-USER uid="100342"></LI-USER> , 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!</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dakotaperryman_0-1635779247325.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/623369iCD6C610DCB455413/image-size/medium?v=v2&px=400" role="button" title="dakotaperryman_0-1635779247325.png" alt="dakotaperryman_0-1635779247325.png" /></span></P><P> </P>Mon, 01 Nov 2021 15:07:52 GMThttps://community.powerbi.com/t5/Desktop/Dynamic-Share-Percentage-Based-on-Hierarchy-Orientation/m-p/2166549#M796368dakotaperryman2021-11-01T15:07:52Z