Forum Discussion

amirghaderi's avatar
amirghaderi
Helper IV
5 years ago
Solved

Incorrect Sum Matrix

How is possible the sum shown in matrix to be incorrect. If I calculate the sum in excel it gives me different number.

the fields are decimal type as well.

The data are columns as well, no measure.

 

Below table from Power Bi and excel manual calc:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi amirghaderi 

    Try to build measures to get Price and H from Table B.

     

    Price From B = CALCULATE(SUM(B[Price]),FILTER(B,B[Category] in VALUES(A[Category])))
    H From B = CALCULATE(SUM(B[H]),FILTER(B,B[Category] in VALUES(A[Category])))

     

    Result:

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • tex628's avatar
    tex628
    Community Champion
    5 years ago

    Hi amirghaderi , 

    This should not be possible, Can you possibly send the file or recreate the issue in a sample file? 

    / J

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amirghaderi 

    Please check your data model. If you use the same data model in Power BI and Excel, you should get the same result in Total.

    I can get correct result in my Power BI Desktop. 

    Result:

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • amirghaderi's avatar
      amirghaderi
      Helper IV
      5 years ago

      I found the reason for discrepancy. Although dont know how to fix it.

      There is a relationship between table A and B (one  to many), The value in matrix visual is from table B which has a grouping refrence to table A value. There is a value in table B  which does not match in table A ( against one to many relationship). So, even the total get calculated in the bottom sum, cant show that in the matrix. not sure if it is possble to be shown with Blank grouping.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi amirghaderi 

        Try to build measures to get Price and H from Table B.

         

        Price From B = CALCULATE(SUM(B[Price]),FILTER(B,B[Category] in VALUES(A[Category])))
        H From B = CALCULATE(SUM(B[H]),FILTER(B,B[Category] in VALUES(A[Category])))

         

        Result:

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.