Getting the correct totals in PowerBI for calculated measures grouped by a set of fields

Totals in Power BI are calculated horizontally instead of vertical sum for the calculated measure grouped by a specific set of fields

We worked on a requirement to get the Vendor Responsibility for the warranty claimed by customers. The data set was as follows:

VendorMaterialCustomerSalesWarrantyVendor Responsibility %Vendor Responsibility (Warranty*Vendor Responsibility)
V1M1C1200550%(5*50%) = 2.5
V1M1C23001050%(10*50%) = 5
V1M1C34001550%(15*50%) = 7.5
V2M1C15002030%(20*30%) = 6
V2M1C21003030%(30*30%) = 9
V2M1C36004030%(40*30%) = 12

In the PowerBI dashboard, we were required to display the data at Vendor, Material level. Vendor Responsibility was a calculated measure where the Vendor Responsibility percentage was fixed at the Vendor level. Therefore, we made it AVG aggregation at totals.

When we looked at the totals in the dashboard, vendor responsibility comes absolutely correct at individual Vendor, Material level. But, it was not coming correct at the overall total due to AVG aggregation on vendor responsibility percentage.

The reason behind that was because of PowerBI calculates measures horizontally instead of vertically as explained below.

 VendorMaterialCustomerSalesWarrantyVendor Responsibility %Vendor Responsibility (Warranty*Vendor Responsibility)
 V1M1C1200550%(5*50%) = 2.5
 V1M1C23001050%(10*50%) = 5
 V1M1C34001550%(15*50%) = 7.5
SubTotal (V1, M1)   9003050%15
 V2M1C15002030%(20*30%) = 6
 V2M1C21003030%(30*30%) = 9
 V2M1C36004030%(40*30%) = 12
SubTotal (V2, M2)   12009030%27
Total   210012040%48

We can see that PowerBI calculates the totals horizontally for the totals as (120*40%) = 48. But, when we take the total sum of the vendor responsibility column then it should be 42.

Solution:

We create a summarized table that provides Vendor Responsibility grouped by Vendor and Material.

Then, we used the Vendor Responsibility coming from the above-summarized table as the total sum.

/*Code Snippet

*** Measure 1 – Vendor Responsibility ***

Vendor Responsibility = VendorMaterial_Table [WARRANTY] * VendorMaterial_Table [VENDOR RESPONSIBILITY %]

*** Measure 2 – Vendor Responsibility – Total Fix ***

Vendor Responsibility – Total Fix =

SUMX(

SUMMARIZE(

VendorMaterial_Table, VendorMaterial_Table [VENDOR],

VendorMaterial_Table [MATERIAL],

“VendorResponsibilityTotal”, [Vendor Responsibility]

        ),

[VendorResponsibilityTotal]

)

*/

RESULT

The overall totals were coming as good as it is calculated vertically like any other individual column in the table. Basically, this was the method of achieving group by in Power BI and showing totals post group by.

 VendorMaterialCustomerSalesWarrantyVendor Responsibility %Vendor Responsibility (Warranty*Vendor Responsibility)
 V1M1C1200550%(5*50%) = 2.5
 V1M1C23001050%(10*50%) = 5
 V1M1C34001550%(15*50%) = 7.5
SubTotal (V1, M1)   9003050%15
 V2M1C15002030%(20*30%) = 6
 V2M1C21003030%(30*30%) = 9
 V2M1C36004030%(40*30%) = 12
SubTotal (V2, M2)   12009030%27
Total   210012040%42

Other Posts from TekLink

"We partnered with TekLink based upon a previous positive experience where they did a great job for us as well as hearing positive feedback about their excellent level of service. We’ve also interviewed many of their experts and consistently found their candidates to be the most technically sound with excellent BW and HANA knowledge above and beyond that of their competitors. Teklink has certainly helped us to stabilize and improve the reliability of our BI operations."
Patrick Bachman
Patrick Bachman

IT Architect

“TekLink’s team exceeded Kellogg Latin America’s expectations with the implementation of Anaplan. Not only their diligence and technical mastery were evident, but also provided critical and out-of-the-box solutions to meet the project’s criteria and expand its scope.”
Francisco Ibarra
Francisco Ibarra

Sr. Manager

“TekLink provided worry free BEx to AO Migration by analyzing and converting our 500+ BEx workbooks to Analysis for Office while also increasing adoption by running power user workshops.”
Lakshmi Thota
Lakshmi Thota

Sr. Manager

Contact Us