2 Replies Latest reply on Aug 15, 2016 9:09 PM by madhuri.tanniru

    Mismatch between Running_Total and Grand Total

    madhuri.tanniru

      Hi,

       

      Please see the attached, dashboard - [GrandTotals]

       

      I have two fields, one is [CNTD(Value)] and another a  calculated field with running total of CNTD(Value).

       

      Now, when I set the option Analysis -> Totals -> Show column grand totals" , I see that the

       

      GrandTotal ( CNTD[Value]) <>  lastvalue of RUNNING_TOTAL(CNTD[Value])

       

      Can someone please explain what's happening here

       

       

      Thanks,

      -Madhuri

        • 1. Re: Mismatch between Running_Total and Grand Total
          Stoyko Kostov

          Hi Madhuri,

           

          The Grand Total in the "Distinct Count of Value" columns is NOT the sum of the "distinct count of value" for each day. It is simply the count of distinct values for the entire year.

           

          The Running Total is the actual sum.

           

          The definitions are different: for example, if you have Day 1 with distinct values A, B, C, and Day 2 with distinct values C, D, E, the overall distinct values are 5 - A through E, but the sum of the daily distinct values is 6 (3+3).

           

          The numbers coincide for 2016 by chance.

           

          You can cross-check my statement in the following way: create a new sheet, drag Year(Order Date) to Columns, and Value to Text. Right-click on the pill and choose COUNT DISTINCT as aggregation. You'll see 253 distinct values for 2015 and 263 for 2016, which are the same values you've highlighted above.

           

          In general, COUNTD is not a good aggregation to compute totals on, as it is not additive - explained in the example I gave above.

           

          Let me know if you have any questions.

          1 of 1 people found this helpful
          • 2. Re: Mismatch between Running_Total and Grand Total
            madhuri.tanniru

            Thanks a lot Stoyko!

             

            Thanks,

            -Madhuri