measure values same on each row

[Greenwoodr]

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

[themistoklis]

@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]

Fact TableOther TablesOther TablesBI DesignActual OutputIDesired Output

[Greenwoodr · Accepted solution]

Hi,

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

Thanks for your time

[Greenwoodr]

Hi,

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

Thanks for your time

[Greenwoodr]

Fact TableOther TablesOther TablesBI DesignActual OutputIDesired Output

[Greenwoodr]

Thanks. 

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

Richard

[themistoklis]

@Greenwoodr

You can use dropbox, onedrive, googledrive,.... or e.g. wetransfer.com

[Greenwoodr]

I have put some screen shots of the problem if that helps.