Forum Discussion

palunel's avatar
palunel
Icon for Helper I rankHelper I
9 years ago
Solved

Inconsistent Calculated Measures

"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...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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's avatar
palunel
Icon for Helper I rankHelper I
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    palunel
    Icon for Helper I rankHelper I
    9 years ago
    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.