2 Replies Latest reply on Aug 23, 2018 12:49 PM by OBRAD ILIC

    Sum of Distinct count

    OBRAD ILIC

      One more time, asking for help here.

       

      1. I have a Measure created by the following function

       

      COUNTD(IF ([Prod]=2) or ([Prod] = 3) THEN ([Username])END)

       

      2. That measure used with Month in Column shelf gives me following result:

        

      JanFebMarAprMayJunJulAug
      76169216767261266079620768995846

       

      3. Going up to menu bar and selecting Analysis/Totals/Show Row Grand Totals/ I get

        

      JanFebMarAprMayJunJulAugGrand Total
      7616921676726126607962076899584623700

       

      4. IMPORTANT  - Going back to menu bar Analysis/Totals/Total All Using/Sum/

        

      JanFebMarAprMayJunJulAugGrand Total
      76169216767261266079620768995846

      55661

       

      5. The question is  - How can I get to This grand total by creating a measure - Calculated field  - with function?

        • 1. Re: Sum of Distinct count
          Naveen B

          Hi Obrad,

           

          I have recreated your problem in order to capture the total value follow the below steps

           

          Step 1: Count of Customers

           

          View: Total is 2626

           

          Create calculated field like below - See without month I am getting the 2626 value

           

          Note: If any dimension filter are there add them to the context

           

          Hope this helps kindly mark this answer as correct/helpful so that it will help others

           

          BR,

          NB

          1 of 1 people found this helpful
          • 2. Re: Sum of Distinct count
            OBRAD ILIC

            Thank you very much Naveen,

            This was helpful, however....

             

            Is there a way to make it fixed LOD ( level of detail) for a year and month ( to be specific at the year/month) level?

             

            Here is what I am talking about - Applying your formula it seems that Fixed Month picks up Moth data from all underlying data and the table then looks like this

                

            20172018Grand Total
            MayJunJulAugSepOctNovDecTotalJanFebMarAprMayJunJulAugTotal
            Users2,3952,5162,5323,6443,2834,0025,4044,461

            28,237

            7,6169,2167,6726,1266,0796,2076,8995,846

            55,661

            83,898

            Users_YTD7,8008,0188,6548,5623,2834,0025,4044,461

            50,184

            7,6169,2167,6726,1317,8008,0188,6548,562

            63,669

            80,819

             

            You see where May, June, July and August are overstated for measure "Users_YTD"?

             

            Where Users_YTD is derived, in part, from your formula

            { FIXED DATEPART('month', [Usage_Date]):([Users])}

             

            and Users is from the above

            COUNTD(IF ([Prod]=2) or ([Prod] = 3) THEN ([Username])END)

             

            Thank you,