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:

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.

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.