Measures Are Superseding My Slicers/Filters

[therightblue]

Background

My fact table is sales quote history, including sales manager, salesperson, customer number, customer name, quote number, quote version, item number, effective, expiry, quote price, item cost, units sold, dollars sold, etc.

A quote number can have many different quote versions. Example: If quote 123, version 0 has its expiry date changed, a new version will be created: quote 123, version 1. That new version will feature the new expiry date. 

Each week we get a new quote file that is uplaoded into our dataset. This file will capture any new quotes/quote versions created, as well as quote usage for that previous week. Each week an item on a quote has usage (dollars sold, units sold), a new row would be created in the file showing that usage. This causes many duplicate rows in our data (duplicate quote number, duplicate item number, duplicate quote quantity, duplicate quote price, etc.). The units sold and dollars sold is never duplicated. To account for duplicated rows, I created a calculated column (ItemQuoteID) which concatenates item number and quote number. 

With this in mind, I created a calculated column (Max Revision Indicator) that will indicate whether the item in the row is from the latest version of the quote. 

Sales Manager and Salesperson information in my fact table is often incorrect. It's a bug within our quote system. As a result, I have a separate dimension table (Salesperson List) that is related to my fact table using customer number:

Salesperson List (One) -> Data/Fact Table (Many)

My Objective

I have to create measures for GPM % of what was quoted:

Sum of Extended Price-Sum of Extended Cost

                   Sum of Extended Price

Because I can have many different versions of a particular quote number, when creating this measure, I only want to calculate the GPM quoted for the latest version of the quote. My series of measures are as follows:

Quote Total Revenue = CALCULATE(SUMX(DISTINCT(Data[ItemQuoteID]),[Max Extended Price]),Data[Max Revision Indicator]=1)

Quote Total Cost = CALCULATE(SUMX(DISTINCT(Data[ItemQuoteID]),[Max Extended Quote Cost]),Data[Max Revision Indicator]=1)

Quote GPM = calculate(([Quote Total Revenue]-[Quote Total Cost])/[Quote Total Revenue],(Data[Max Revision Indicator])=1)

The measure seems to work well.

I also have to create a measure for GPM of Items Sold. Because dollar and unit usage can occur over numerous different quote versions, I cannot merely filter by the latest quote version. I have to consider all quote versions. Also have to keep in mind that not all items on a quote have usage. 

Here are my series of measures:

Usage Total Revenue = sumx(data,Data[Total Quote Units Sold]*Data[Quote Price])

Usage Total Cost = sumx(data,Data[Total Quote Units Sold]*Data[Item Cost])

Usage GPM = if([Usage Total Revenue]=0,0,calculate(([Usage Total Revenue]-[Usage Total Cost])/[Usage Total Revenue],all(Data[Max Revision Indicator])))

Again, this measure seems to work.

Here is my problem. When I create a table, if I were to bring in Sales Manager or Salesperson from my dimension table (Salesperson List), it doesn't seem to relate.

Example:

If I create table with the following columns:

Sales Manager (Salesperson List table)

Salesperson (Salesperson List table)

Customer Number (Data/Fact table)

Quote Number (Data/Fact table)

Quote GPM (Measure)

Usage GPM (Measure)

My table will actually erroniously show all salespersons for every quote number. Only the correct sales manager and salesperson in the list will have the Quote GPM and Usage GPM populated. All the other rows with the erronious salespersons have a blank Quote GPM and $0.00 for Usage GPM. 

A quote number is tied to one customer account number, which is tied to one sales manager and one salesperson. 

Same thing happens if I put sales manager or salesperson in a slicer. It has no effect. Even though the Sales Manager might only show as the one selected value, all salespersons are listed, even thought they have no connection to this custome rnumber or quote number. 

I'm thinking that my measures are removing filters, but I cannot figure out how. 

Below are my relationships:

I know that I can merely set a filter for Quote GPM to 'Is Not Blank' and it fixes the issue in my visualization, but I'm concerned that my measures could stand to be improved to fix this issue. I want my measures to work in a way that is not negatively impacting relationships with my dimension tables.

[v-jingzhang]

Hi @therightblue 

I think the cause may be the relationship between tables. Try modifying the relationship's cross-filter direction from Single to Both between Data (Customer Number) and Salesperson Table (Customer Number). Let me know if this works.

Regards,
Community Support Team _ Jing Zhang

[therightblue]

Source.Name,Quote Number,Effective Date,Expiration Date,Item Number,Quote Quantity,Total Quote Units Sold, Extended Price ,Total Quote Dollars Sold,Quote Price,Quote Version,Item Cost,MPG,ItemQuoteID,Max Revision Indicator,Extended Quote Cost
QuotesLink30Jan-6Feb.txt,100164737,1/1/2021,6/30/2021,Test Item,50,0,$405.00 ,0,8.1,3,1.3981537,73,Test Item100164737,1,69.907685
QuotesLink10-16Jan_2.txt,100164737,1/1/2021,6/30/2021,Test Item,50,0,$405.00 ,0,8.1,1,1.3981537,73,Test Item100164737,0,69.907685
QuotesLink03-09Jan.txt,100164737,1/1/2021,6/30/2021,Test Item,50,0,$405.00 ,0,8.1,0,1.3981537,73,Test Item100164737,0,69.907685