none
Calculated measures problem RRS feed

  • Question

  • Hi, 

     

    I have an issue around the context of calculated measures which I am struggling to get my head around.

     

    I have the following

     

    1. Fact table (sales)

        Customer ID

        Date ID

        ProductID

        Leads Flag (0,1)

        Sales Flag (0,1)

    2. Date Dimension

        DateID

       [Various date variables]

    3. Seller Table

       SellerID

     Sellers Name

    4. Customer table

       CustomerID

       PhoneNumberFlag (contains 0s & 1s)

     

       This allows me to create the following table by product and date range (i.e Weeknum):

     

    Seller       Count leads    count Sale     Percentage(measure)

    Bob             10                    8                      80%

    Dave             2                     1                      50

     

    measure = Sum(sales)/sum(leads)

     

    I need to know how many leads have a phone number  . However when I do this I get the same answer across all rows of the table and am therefore igoring the filter context of the table

     

    Seller       Count leads    count Sale     Percentage(measure)    sum of PhoneNumberFlag

    Bob             10                    8                      80%                               80000

    Dave             2                     1                      50                                 80000

     

    sum of PhoneNumberFlag = calculate(sum('Customer table'[PhoneNumberFlag]))

     

    How do I ensure this measure is in the context of the Table in the report?

     

    Many Thanks

     

    Tuesday, January 22, 2019 12:58 PM

Answers

  • Hi greenwoodr,

    As far as I understand, you got this result, because there is no context transition.

    Besides, I guess, you should replace fields in pivot table - Seller for CustomerID (from Customer table).


    Tuesday, January 22, 2019 2:32 PM

All replies

  • Hi greenwoodr,

    As far as I understand, you got this result, because there is no context transition.

    Besides, I guess, you should replace fields in pivot table - Seller for CustomerID (from Customer table).


    Tuesday, January 22, 2019 2:32 PM
  • Hi greenwoodr,

    Any query ?


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Sunday, October 13, 2019 2:35 PM
    Answerer