Inconsistent Calculated Measures

[palunel]

"I get inconssistent results when using calucalted measures.  In the table below

the last 5 coloumns are all calculated measures.  Coloumns "CAPEX", "OPEX" and "Days" totals completely incorrect.  Below is the same table where I exported this exact table using the "Export Data" function to Excel and totalled the same coloumns:

Any ideas how this can be possible?  The individual rows are correct.  The totals are wrong.

Paul

[GilbertQ · Accepted solution]

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

[palunel]

---

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

[palunel]

---

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

[parry2k]

Hey Paul, share your measure calculation to understand what is going on

[palunel]

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

[Anonymous]

Is your [Ave Occurence] an average based measure?  Could the average be changing based on the usage on the row by row nature of your matrix?

[palunel]

---

@Anonymous wrote:

Is your [Ave Occurence] an average based measure?  Could the average be changing based on the usage on the row by row nature of your matrix?

---

No, [Ave Occurence] is  a non-calculated coloumn resulting from the simmulation that generates the data to be visualised.