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

# Sum of Distinct count

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:

 Jan Feb Mar Apr May Jun Jul Aug 7616 9216 7672 6126 6079 6207 6899 5846

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

 Jan Feb Mar Apr May Jun Jul Aug Grand Total 7616 9216 7672 6126 6079 6207 6899 5846 23700

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

 Jan Feb Mar Apr May Jun Jul Aug Grand Total 7616 9216 7672 6126 6079 6207 6899 5846 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

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

Thank you very much Naveen,

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

# 83,898

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

# 50,184

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

# 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,