cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dakotaperryman
Frequent Visitor

Dynamic Share Percentage Based on Hierarchy Orientation

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_0-1635423149657.png

3 REPLIES 3
dakotaperryman
Frequent Visitor

YearCompanyNamePromotional ChannelSpecialty Short NameSum of Dollars
2019Company_ADTC Specialty_167939157.82
2019Company_AePromoSpecialty_2681000
2019Company_AePromoSpecialty_3639000
2019Company_AePromoSpecialty_4132000
2019Company_AePromoSpecialty_590000
2019Company_AEventSpecialty_35693640
2019Company_AEventSpecialty_5998190
2019Company_ASampleSpecialty_2197410.1971
2019Company_ASampleSpecialty_6550195.644
2019Company_ASampleSpecialty_52815495.896
2019Company_BDTC Specialty_1569070082.6
2019Company_BePromoSpecialty_7125000
2019Company_BePromoSpecialty_2568000
2019Company_BePromoSpecialty_33540000
2019Company_BePromoSpecialty_41111000
2019Company_BePromoSpecialty_5332000
2019Company_BEventSpecialty_215329912
2019Company_BEventSpecialty_42291965
2019Company_BEventSpecialty_513618270
2019Company_BJournalSpecialty_14760607
2019Company_BSampleSpecialty_24015363.119
2019Company_BSampleSpecialty_66075946.901
2019Company_BSampleSpecialty_43179414.932
2019Company_BSampleSpecialty_510539749.79
2019Company_CePromoSpecialty_2276000
2019Company_CePromoSpecialty_590000
2019Company_CEventSpecialty_41165494
2019Company_CEventSpecialty_52956546
2019Company_CJournalSpecialty_1839588
2019Company_CSampleSpecialty_49109703.854
2019Company_CSampleSpecialty_54592272.89
2020Company_ADTC Specialty_1103991912
2020Company_AePromoSpecialty_299000
2020Company_AePromoSpecialty_3583000
2020Company_AePromoSpecialty_4108000
2020Company_AePromoSpecialty_5331000
2020Company_AEventSpecialty_3306843
2020Company_AEventSpecialty_44968346
2020Company_AEventSpecialty_52389557
2020Company_ASampleSpecialty_2716827.8962
2020Company_ASampleSpecialty_6490286.8177
2020Company_ASampleSpecialty_51803054.002
2020Company_BDTC Specialty_1501801145.9
2020Company_BePromoSpecialty_2206000
2020Company_BePromoSpecialty_61878000
2020Company_BePromoSpecialty_41221000
2020Company_BePromoSpecialty_5495000
2020Company_BEventSpecialty_29400229
2020Company_BEventSpecialty_43122809
2020Company_BEventSpecialty_54264413
2020Company_BJournalSpecialty_12426714.5
2020Company_BSampleSpecialty_27655083.042
2020Company_BSampleSpecialty_61227377.427
2020Company_BSampleSpecialty_55856243.951
2020Company_CDTC Specialty_1621000
2020Company_CePromoSpecialty_2305000
2020Company_CePromoSpecialty_6935000
2020Company_CePromoSpecialty_5115000
2020Company_CEventSpecialty_4119653
2020Company_CEventSpecialty_53175197
2020Company_CJournalSpecialty_11665235.1
2021Company_ADTC Specialty_159853083.4
2021Company_AePromoSpecialty_3230000
2021Company_AePromoSpecialty_4371000
2021Company_AePromoSpecialty_567000
2021Company_AEventSpecialty_5446205
2021Company_ASampleSpecialty_51498953.013
2021Company_BDTC Specialty_1161882701.3
2021Company_BePromoSpecialty_2225000
2021Company_BePromoSpecialty_64174000
2021Company_BePromoSpecialty_3282000
2021Company_BePromoSpecialty_4444000
2021Company_BePromoSpecialty_517000
2021Company_BEventSpecialty_2387588
2021Company_BEventSpecialty_41277002
2021Company_BJournalSpecialty_1743896.25
2021Company_BSampleSpecialty_2976907.9627
2021Company_BSampleSpecialty_66214636.555
2021Company_BSampleSpecialty_521724937.82
2021Company_CDTC Specialty_11066055.72
2021Company_CePromoSpecialty_289000
2021Company_CePromoSpecialty_6853000
2021Company_CePromoSpecialty_498000
2021Company_CJournalSpecialty_1524470
2021Company_CSampleSpecialty_639974041.59

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!

 

dakotaperryman_0-1635779247325.png

 

lbendlin
Super User
Super User

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. 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!