Aggregation using Sumproduct() in Power BI

Question

I'm working on creating a Power BI report to emulate an existing Excel report. In the excel report, they are using the sumproduct() function to calculate and aggregate the data to determine MyCompany's price index on the product and market level. The data set is as follows:&nbsp;&nbsp;1st Lev aggregation = sumproduct(1st Lev Units,1st Lev Index)/sum(1stLev Units)Rival1 &amp; 2= ProvidedItem = Provided1st Level Units = Provided1st Level Index = Provided1st Level Line Weight = 1st Level Units * 1st Level Index for Rival1 and Rival21st Level Weighted Average = Sum 1st Level Line Weight / Sum 1st Level Units&nbsp;2nd Lev aggregation = sumproduct(2nd Lev Units, 2nd Lev Index)/sum(2ndLev Units)MyCompany = Provided1st Level Weighted Average = Calculated (by Rival#)2nd Level Line Weight = 1st Level Weighted Average (by Rival#)* MyCompany Units2nd Level Weighted Average = Sum 2nd Level Line Weight / Sum MyCompany Units&nbsp;Company &nbsp; &nbsp; &nbsp; Item &nbsp; Units &nbsp; Index &nbsp; &nbsp; 1st Lev Line Wt &nbsp; &nbsp; 1st Sum Line Wt &nbsp; &nbsp; &nbsp; 1st Sum Units &nbsp; &nbsp; 1st Lev Wt Ave &nbsp;MyCompany&nbsp; A85 &nbsp;&nbsp; 120 &nbsp; &nbsp;&nbsp;Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B23 &nbsp;&nbsp; 150 &nbsp; &nbsp;&nbsp;0.8506 &nbsp; &nbsp; 127.59Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B44 &nbsp;&nbsp; 73 &nbsp; &nbsp; &nbsp;&nbsp;0.9584 &nbsp; &nbsp;&nbsp; 69.9632Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B75 &nbsp;&nbsp; 22 &nbsp; &nbsp; &nbsp;&nbsp;0.8756 &nbsp; &nbsp;&nbsp; 19.2632 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 216.82 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 245 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.8850Rival2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C18 &nbsp;&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 1.0596 &nbsp; &nbsp;&nbsp; 2.1192 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2.1192 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1.0596&nbsp;MyCompany &nbsp; A66 &nbsp; &nbsp; 85 &nbsp; &nbsp; &nbsp;Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B23 &nbsp;&nbsp; 150 &nbsp; &nbsp;&nbsp; 0.8506 &nbsp; &nbsp;&nbsp; 127.59Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;B44 &nbsp; &nbsp; 73 &nbsp; &nbsp; &nbsp;&nbsp;0.9584 &nbsp; &nbsp;&nbsp; 69.9632Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B99 &nbsp; &nbsp; 15 &nbsp; &nbsp; &nbsp; 1.0468 &nbsp; &nbsp; &nbsp; 15.702 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 213.82 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 238 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.8960Rival2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;C18 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 1.0596 &nbsp; &nbsp;&nbsp; 2.1192 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2.1192 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1.0596&nbsp;MyCompany &nbsp; A77 &nbsp; &nbsp; 65Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B44 &nbsp; &nbsp; 73 &nbsp; &nbsp; &nbsp;&nbsp;0.9584 &nbsp; &nbsp;&nbsp; 69.9632Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B99 &nbsp; &nbsp; 15 &nbsp; &nbsp; &nbsp; 1.0468 &nbsp; &nbsp;&nbsp; 15.702 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 85.665 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 88 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0.9735Rival2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C18 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 1.0596 &nbsp; &nbsp;&nbsp; 2.1192Rival2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; C33 &nbsp; &nbsp; 120 &nbsp; &nbsp;&nbsp; 0.9951 &nbsp; &nbsp; 119.412 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 121.53 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1&nbsp;Company &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Item &nbsp; Units &nbsp; Rival Index &nbsp; &nbsp; 2nd Lev Line Wt &nbsp; &nbsp; 2nd Sum Line Wt &nbsp; &nbsp; &nbsp; 2nd Sum Units &nbsp; &nbsp; 2nd Lev Wt AverageMyCompany-Rival1 &nbsp; &nbsp; A85 &nbsp; &nbsp; 120 &nbsp;&nbsp; 0.8850 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 106.20MyCompany-Rival1 &nbsp; &nbsp; A66 &nbsp; &nbsp;&nbsp; 85 &nbsp; &nbsp;&nbsp; 0.8960 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 76.16MyCompany-Rival1 &nbsp; &nbsp; A77 &nbsp; &nbsp;&nbsp; 65 &nbsp; &nbsp; 0.9735 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 63.28MyCompany-Rival2 &nbsp; &nbsp; A85 &nbsp; &nbsp;&nbsp; 120 &nbsp;&nbsp; 1.0596 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 127.15MyCompany-Rival2 &nbsp; &nbsp; A66 &nbsp; &nbsp;&nbsp; 85 &nbsp; &nbsp;&nbsp; 1.0596 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 90.07MyCompany-Rival2 &nbsp; &nbsp; A77 &nbsp; &nbsp;&nbsp; 65 &nbsp; &nbsp; &nbsp; 0.9962 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 64.75&nbsp;MyCompany-Rival1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 245.63 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 270 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.9098MyCompany-Rival2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 281.97 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 270 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0443&nbsp;MyCompany &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 527.6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 540 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.9770&nbsp;I am able to complete the 1st level aggregation process, however I am having trouble completing the 2nd level aggregation. My problem is finding a way to aggregate the rival index (1st Lev Weighted Average) to the 2nd level to complete the calculations. If you know of a way to get this completed, I would greatly appreciate the advice,&nbsp;Indraneel Dutt&nbsp;

sivamani · Answer

Anonymous,&nbsp;Check this function -&nbsp;https://docs.microsoft.com/en-us/dax/sumx-function-daxSUMX(&lt;table&gt;, &lt;expression&gt;)&lt;expression&gt; -&gt; you can add calculations such as&nbsp;1st Level Units * 1st Level Index for Rival1 and Rival2&nbsp;&nbsp;Hope it will help you!

amitchandak · Answer

You can use sumx to have mutiplcation od measures. Refer to this post to have different formula at different level

https://community.powerbi.com/t5/Desktop/Measure-logic-for-different-hierarchy-level/td-p/699606

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

anonymous · Answer

Hello all,&nbsp;&nbsp;Thank you for your suggestions to use sumx() as a means to generate the needed subtotals for my exercise. I did have one follow up question, have either of you experienced difficulty of performing a sumx() function using input from a column generated by a sumx() measure?&nbsp;In my example, I'm trying to generate the value for the 2nd Sum of MyCompany Line Weight using the sumx() function, but Power BI is not recognizing the MyCompany Line Weight column. Any suggestions?&nbsp;Indraneel Dutt

Forum Discussion

Aggregation using Sumproduct() in Power BI

3 Replies

Related content

Using Aggregate Tables and Partitions

Data Aggregation issue

Using SUMPRODUCT in dax

Aggregate

SumProduct in measure