Measure subtotal doesn't equal the components!!!!

[masplin]

I've got a really weird situation that I can't explain and must be something to do with context. I have created a new measure, but the total of the rows does not equal the grand total. I am sure the grand total is correct so for some reason the rows are not always showing a value when they should do. It looks like this and the last column is my new measure

[TABLE="width: 500"]
<tbody>[TR]
[TD]

User ID	Activated Users	Spend Users 14-44 Days

<tbody>
[TD="class: xl381"]1111243[/TD]
[TD="class: xl382, align: right"]1[/TD]

[TD="class: xl381"]1226023[/TD]
[TD="class: xl382, align: right"]1[/TD]

[TD="class: xl381"]1243663[/TD]
[TD="class: xl382, align: right"]1[/TD]

[TD="class: xl381"]1244363[/TD]
[TD="class: xl382, align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl381"]1244623[/TD]
[TD="class: xl382, align: right"]1[/TD]

[TD="class: xl381"]1244783[/TD]
[TD="class: xl382, align: right"]1[/TD]

[TD="class: xl381"]1244943[/TD]
[TD="class: xl382, align: right"]1[/TD]

[TD="class: xl381"]1245193[/TD]
[TD="class: xl382, align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl381"]Grand Total[/TD]
[TD="class: xl382, align: right"]8[/TD]
[TD="align: right"]3[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

I have 2 tables "Users" and "Transactions" where USER ID is the related field. Activation Day is the day the user first buys, transactions[date] is the date of buying. I want to count which of the users buy again 14 to 44 days later. The table above is filter for just one activation day which is 15/4/14. My measure is

CALCULATE(
                        CALCULATE(DISTINCTCOUNT(transactions[User ID]),
                                             FILTER(RELATEDTABLE(transactions),
                                                           RELATED(Users[Activation Day])+14<transactions[date]  &&
   < transactions[Date]  &&    
                                                           RELATED(users[Activation Day])+44 > transactions[Date]  &&
                                                            transactions[Transaction]="spend"
                                                          )
                                             ),
                        FILTER(
                                     users,
                                     users[1st Spend]+44<max(transactions[date])
 < max(transactions[Date])
                                     )
                        )

</max(transactions[date])


I think i am creating a table of related transactions for my 8 users where the transaction date falls more than 14 and less than 44 days after this activation date and they are spending. I then calculate the distinct users in this table. The last part is to only do this calculation on users who activated at least 44 days ago.

To check what the result should be I ran this pivot for these 8 users to find the days they bought on. The window should be 29/4 to 29/5. So you can see that user 1244943 bought on 2/5 and should show a 1 for my measure but it is blank. However the total is correct as 3. I have test several other days with similar results.

[TABLE="width: 500"]
<tbody>[TR]
[TD]

Purchases

User ID

<tbody>

Transaction [date]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]

[TD="class: xl382, align: right"]15/04/2014[/TD]
[TD="class: xl382, align: right"]27/04/2014[/TD]
[TD="class: xl382, align: right"]02/05/2014[/TD]
[TD="class: xl382, align: right"]15/05/2014[/TD]
[TD="class: xl382, align: right"]26/05/2014[/TD]

[TD="class: xl383"]1111243[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1226023[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1243663[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1244363[/TD]
[TD="class: xl384, align: right"]5[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384, align: right"]5[/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1244623[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1244783[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1244943[/TD]
[TD="class: xl384, align: right"]5[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]

[TD="class: xl383"]1245193[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384, align: right"]2[/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384"][/TD]
[TD="class: xl384, align: right"]1[/TD]

[TD="class: xl383"]Grand Total[/TD]
[TD="class: xl384, align: right"]16[/TD]
[TD="class: xl384, align: right"]2[/TD]
[TD="class: xl384, align: right"]1[/TD]
[TD="class: xl384, align: right"]5[/TD]
[TD="class: xl384, align: right"]1[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
I just cannot understand how the total is NOT the sum of the rows? If anyone can shed any light light i would really appreciate as hard to trust the output. I'm using Excel 2013

P.S. When I preview the code is not complete so this may not come out right!

mike
</transactions[date]>

 

[XLBob]

Can you let me know what columns each table has?

 

[masplin]

If you mean the user and transaction table they have loads but the relevant ones are

Users: User ID, Activation Day (rounded 1st Spend to be whole day), 1st Spend (exact time of activation)

Transactions: User ID (related to users[user id]), date, transaction (spend, refund etc)

I've paste the measure without using the code# so you can see it correctly.

CALCULATE(
CALCULATE(DISTINCTCOUNT(transactions[User ID]),
FILTER(RELATEDTABLE(transactions),
RELATED(Users[Activation Day])+14<transactions[date] &&
RELATED(users[Activation Day])+44>transactions[Date] &&
transactions[Transaction]="spend"
)
),
FILTER(
users,
users[1st Spend]+44<max(transactions[date])
)
)

Thanks
Mike</max(transactions[date])
</transactions[date]>

 

[XLBob]

Mike, I attahced my file in below. You can download and play around. I am in the middle of trying to figure out why the Grand Total is blanked out for my measure [Spend User 14-44 Days]
https://onedrive.live.com/?cid=7DF5E31C189063EC&id=7DF5E31C189063EC%21124

 

[scottsen]

Looks like your measures are pasting weird on the forums. This "reads" correct to me, and should work fine?

UsersInWindow:=CALCULATE(DISTINCTCOUNT('Transaction'[UserID]), 
                 FILTER('Transaction', 
                     'Transaction'[Date] >= RELATED(User[ActivationDate]) + 14 && 
                     'Transaction'[Date] <= RELATED(User[ActivationDate]) + 44 &&
                     'Transaction'[UserID] = RELATED(User[User ID])))

 

[XLBob]

Hi Scott,I have tested your measure and it worked.I also fixed my measure because of your post. well done. happy days

 

[masplin]

Hi Scott

I'll try again. This is my measure

CALCULATE(
CALCULATE(DISTINCTCOUNT(transactions[User ID]),
FILTER(RELATEDTABLE(transactions),
RELATED(Users[Activation Day])+14<transactions[date] &&
< transactions[Date] &&
RELATED(users[Activation Day])+44>transactions[Date] &&
transactions[User ID]=RELATED(users[User ID]) &&
transactions[Transaction]="spend"
)
),
FILTER(
users,
users[activation day]+44<max(transactions[date])
)
)</max(transactions[date])
</transactions[date]>

so I added trans[user id]=users[user id] and still have the same issue. I wouldnt have thought I need this as using RELATEDTABLE in the FILTER.

 

[masplin]

I tried taking the RELATEDTABLE out and no difference.

 

[scottsen]

Is there something about the measure I pasted that is not working?

In what you just pasted, this reads REAL weird to me:
FILTER(users, users[activation day]+44<max(transactions[date])
)</max(transactions[date])

 

[masplin]

Hi Scott

The reason I wrapped the main calaution in this extra Filter is i want to exclude say users who activated 30 days ago i.e. they don't have a full 44 day history. I think this FILTER will turn any of these to blanks.

Maybe it didnt paste right so should read

FILTER(
users,
users[1st Spend]+44<max(transactions[Date])
)

<max(transactions[date])





However if I delete this part it works fine. So question is why</max(transactions[date])
does this blank out just one of the 8 users who activated on this day as you would expect the formula to be the same


ah got it. Using max(transactions[date]) when you are looking at row with a specific user is calculating the max of the transactions for just that user. What i meant was the max of all transactions i.e. the last date in my transaction list. So if I change the last FILTER to

  FILTER(
                                     users,
                                     users[1st Spend]+44<
                                      CALCULATE(                                             
                                                           max(transactions[Date]),
                                                           ALL(transactions)
                                                            )
                                    )

it works. so it was a context problem!!!! Thanks for helping me to see the light.