locked
"Cleaning" unlinked data RRS feed

  • Question

  • Dear all,

    I'm looking at using PowerPivot to link a list of customers we have emailed to any sales they have made since the emailing. This is very straight forward to do. However PowerPivot groups together any sales made to customers who have not been emailed into one entry which is blank.

    I have linked the sales data set email field to the email data set and then used the email field from the email data set on the Pivot.

    Can anyone suggest a way of tidying this up?

    I can create a calculated column in the emailing data set with a static value and then use this as a filter in the Pivot but I'd like to see sales to customers we've mailed versus those we haven't.

    I hope this makes sense!

    Thanks,

    Matt

    Friday, September 3, 2010 1:40 PM

Answers

  • Im not really sure what you mean. But I think you have 2 sets of data, one with sales by customers and one with email that are used for a mailing. 

    You have created a relationship between the two using the mail address. Now you want to the create a pivot table where you want to see the amount of sales from people who have been sent an email versus the sales from people who haven't been mailed ?

    What i would do is create a calculatedcolumn in the sales table using:

    =IF(RELATED(SentMailTable[column]) = BLANK(),"No mail sent","Mail sent")

    This checks if a value can be found in the mail sent table using the relationship, if he finds nothing you fill the column with "No mail sent" otherwise with "Mail sent".

     

    Is this what you ment ?

     

    • Marked as answer by MAQ1 Wednesday, September 8, 2010 9:30 AM
    Monday, September 6, 2010 11:44 AM

All replies

  • Im not really sure what you mean. But I think you have 2 sets of data, one with sales by customers and one with email that are used for a mailing. 

    You have created a relationship between the two using the mail address. Now you want to the create a pivot table where you want to see the amount of sales from people who have been sent an email versus the sales from people who haven't been mailed ?

    What i would do is create a calculatedcolumn in the sales table using:

    =IF(RELATED(SentMailTable[column]) = BLANK(),"No mail sent","Mail sent")

    This checks if a value can be found in the mail sent table using the relationship, if he finds nothing you fill the column with "No mail sent" otherwise with "Mail sent".

     

    Is this what you ment ?

     

    • Marked as answer by MAQ1 Wednesday, September 8, 2010 9:30 AM
    Monday, September 6, 2010 11:44 AM
  • Kasper,

    That's great thankyou!

     

    Matt

    Wednesday, September 8, 2010 9:31 AM