Forum Discussion

Greenwoodr's avatar
Greenwoodr
Helper I
7 years ago
Solved

measure values same on each row

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

 

  • Greenwoodr's avatar
    Greenwoodr
    7 years ago

    Hi,

     

    I have been playing and Cross directional filtering on the customer table seems to give me the right answer.

    Thanks for your time

     

8 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion

    Greenwoodr

     

    CALCULATE function needs to be used in combination with a FILTER or All/Allexcept/Allselected function.

     

    Any chance to share the file with us or send us some sample data and the expected output?

     

    e.g.

    PhoneNumberFlag = calculate(sum('Customer table'[PhoneNumberFlag]), ALLEXCEPT('Seller Table', 'Seller Table'[Seller Name]))
    • Greenwoodr's avatar
      Greenwoodr
      Helper I

      Thanks. 

       

      I have recreated the problem and put the tables data in Excel - what is the best way to share it?

      Richard

    • Greenwoodr's avatar
      Greenwoodr
      Helper I

      Fact TableOther TablesOther TablesBI DesignActual OutputIDesired Output

    • Greenwoodr's avatar
      Greenwoodr
      Helper I

      Fact TableOther TablesOther TablesBI DesignActual OutputIDesired Output

      • Greenwoodr's avatar
        Greenwoodr
        Helper I

        Hi,

         

        I have been playing and Cross directional filtering on the customer table seems to give me the right answer.

        Thanks for your time