Inconsistent Calculated Measures

---

@guavaq wrote:

Hi palunel

 

If the totals are incorrect on your Matrix but correct for each row, then it is quite possible that it is due to the way the calculation is being filtered correctly for each row.

But when you get to the Totals, there is no row context for your measure to calculate the total correctly.

 

I would suggest reading this blog post by Power Pivot Pro where they do a great job explaining why the values are correct in the Row context, but then appear to be wrong in the Totals. When essentially what you have asked the measure to do, is doing what it should.

 

They have a solution which I have used multiple times in order to get the correct values based on if it is on a Row or a Total.

 

Here is their DAX solution, with the blog post link here: Subtotals and Grand Totals That Add Up “Correctly”

 

[Sales per Day] = 
IF(COUNTROWS(VALUES(Category[Name]))=1,
   [Sales],
   SUMX(VALUES(Category[Name]), [Sales])
)

---

Thanks GilbertQ but I do not think this is the problem (although the suggestion led to a lot of interesting reading!).

Since both [Ave Occurence] and [Most Likely CAPEX] in the example above are uncalulated coloumns, I changed from using SUMX to using only SUM since I do not require any filtering this should be fine.  I get the same incorrect result though.

So I do not really understand where my lack of understanding lies...