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])
)

---

Actually after reading some more and implementign this solution it now gives me the correct answers.  Thanks.

---

@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...

So this one totals wrong:

Base CAPEX Cost = SUMX('Base Risk Occurance Data','Base Risk Occurance Data'[Ave Occurence])*SUMX(Base,Base[Most Likely  CAPEX])

and this on totals correctly

Base Total Cost = SUMX('Base Risk Occurance Data',[Base CAPEX Cost]+[Base OPEX Cost]+[Base UCLF Cost])

But it is important to note that the individual rows in teh table are actually correct.  It is just the totals (and hence if I use a card visualisation) that is wrong.

Paul