Sum problem in matrix table

[OSS]

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.

[OSS]

Actually amounts in my table are result of calculations. In order to give more clear picture of my question I would like to add model itself.

https://1drv.ms/u/s!Anq8qDsGb04lg2B17VggFAB46Zu9?e=dQ8coS

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

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

Hope this helps.

[OSS]

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]

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

[OSS]

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]

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]

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]

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]

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]

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