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

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?

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

I think the correct answer should be 7,39,03,024.  On the top right hand side of your visual, you will see 3 dots.  Click on those 3 dots and select Export Data.  Open the Excel file and create a Pivot Table.  The total of the Grand Total column will be 7,39,03,024.

  • OSS's avatar
    OSS
    Helper III
    7 years ago

    Dear Ashish_Mathur 

    I have checked, you are right. It seems there is something wrong with my upload file. Actually the file I uploaded contains different amount. It seems I need to review upload file.

     

    Thank you very much for your priceless help and patience. 

     

    Best Regards

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    You are welcome.  If my reply helped, please mark it as Answer.