Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Example:
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 |
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!
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.
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |