Forum Discussion
Helper ITotal in a matrix is not considering negative values
Hi
I have a matrix with a range of numbers. The problem is that the total sums up all numbers as if they were were postive.
Problem: 2 735 000 + (-3 213 204) = 5 948 204
Should have been: 2 735 000 + (-3 213 204) = -478 204
All my number were origianally postive because my dataset wasn't that good. In order to change cost into a negative number, I have made a table with 1's and -1's and I change my values accordingly.
To test that my negitive number are actually negative I have:
- used filters on my values >0 and <0. Both work as they should be
- sum up cost and revenue seperately, and they both show up as negative and positve (but they already do that in the matrix, so no surprise there)
- tested how the values look in a collumn chart, and the negative values shows correctly
Any help is appreciated
Thanks,
Espen
11 Replies
- Greg_Deckler
Community Champion
Is your numeric column based on a measure or a column? If it is measure, technically it is not really summing up your numbers, it is essentially evaluating the measure based upon the context of ALL(). See my post here for a more detailed explanation:
http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376
CavringThank you Smoupre
My values are a measure. Your post explains it, so I played around with some very simple data from Excel to see if it would make any difference but with no luck.
What I am trying to do is to make a table/matrix that shows a company's cost and revenue with the sum at the bottom. Is there an easy way to do that?
Hi Cavring,
In order to provide appropriate method for your sceanrio, could you please share sample data of your original tables and post expected result here? And we will appreciate it if you can post the formulas that you use to calculate Total Sum med fortegn measure.
Thanks,
Lydia Zhang- Cavring
Hi Anonymous.
Thanks for your reply.
This is the result I am after (see bottom matrix in the screen clip):
During the day I have solve the problem, I believe there was a problem with the values being a 'measure'. I turned my values into a new collumn and that solved the problem.
Nevertheless, if you look at the above sreen shot from the incorrect data, there are 4 totals (in bold)
- Total 1 is correct
- Total 2 1 228 625 is not corret. It sums up negative numbers only and shows as a positive total
- Total 3 -678 664 is correct. Negative numbers and a negative totalt
- Total 4 is the total ove everything and sums up everything as if they were positve
Eventhough my mesure is not the way to go about this, the above example seems very inconsistent.
Hi Cavring,
You add date columns to the Matrix visual and the totals are not correct. How do you store the data in your original table? And what do you mean by "I turned my values into a new collumn" ?
Thanks,
Lydia Zhang
I actually want to achieve the way your problem shows. What I have is the expenses being positive, but still can show Total as Revenue minus Expense (net amount). I think your problem is exactly what I expect. Can you tell me how you achieve that please?
