Forum Discussion

OSS's avatar
OSS
Helper III
7 years ago
Solved

Sum problem in matrix table

Dear All I have trouble with the matrix table.  In this table sum of rows are ok, even total grand also correct. However Total sum for each column is wrong. Please help me to solve this problem. ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Try this measure

    OPEX_ = SUMX(VALUES(Product_Group[Product_name]),[Per_Unit (main)]*[Deal#count]*-1)

    Hope this helps.

OSS's avatar
OSS
Helper III
7 years ago

Dear Ashish_Mathur 

Thank you very much for your reply. Actually your formula works for my table. Total sum for columns are right. 

But my total Opex amount is changed. 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

You are welcome.  I just checked - no numbers in the matrix have changed.  Please recheck.

  • OSS's avatar
    OSS
    Helper III
    7 years ago

    Dear Ashish_Mathur

    When remove all filters OPEX amount is equal to 76.466.791 (with the formula  [Per_Unit (main)]*[Deal#count]*-1) 

     

    After changing formula with the SUMX(VALUES(Product_Group[Product_name]),[Per_Unit (main)]*[Deal#count]*-1)

    total OPEX amount equals 73.911.984

     

    for checking please remove product group and segment filters.

    Best Regards

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      WIth all filters removed, I think the correct result should be 7,39,03,022.92.  This is the measure i used

      Measure = if(HASONEVALUE(Product_Group[Product_name]),[OPEX_],SUMX(SUMMARIZE(VALUES(Product_Group[Product_name]),Product_Group[Product_name],"ABCD",[OPEX_]),[ABCD]))

      Please reconfirm

      • OSS's avatar
        OSS
        Helper III
        7 years ago

        Hi Ashish_Mathur 

        Actually correct result should be 76.466.791

         

        Total Calculated_Opex is the my main amount. After dividing by product I get Per_unit price 47.79

        Then again I want to multiply per_unit*count=OPEX

        which should be the same amount with the calculated Opex.

         

        However when I use [Per_Unit (main)]*[Deal#count] formula my total sum is correct, but sum for columns are wrong.

        When I use your formula SUMX(VALUES(Product_Group[Product_name]),[Per_Unit (main)]*[Deal#count], the total some is wrong but sum for columns are correct.

         

        Why my opex amount reduced with your formula?