Non-additive Sum of Sales Measure

[Anonymous]

Hi,

I'm struggling with this strange data modeling situation. I have 3 tables:

1. Hierarchy Table - details which sales people each manager manages
2. Product Table - details which product each manager manages
3. Sales Table - details each sale and what region, sales people and product it involved

There is plenty of overlap and I need to aggregate each manager's sales data while avoiding double counted sales. I've linked my dashboard below, any help on this would be greatly appreciated. 

Link to Dashboard: https://www.dropbox.com/s/vjixv9vlmgf38yf/Demo%20Presentation.pbix?dl=0

This is my desired result table, but I can't seem to get it without double counting or just getting the total of their employees only:

[v-lili6-msft]

hi  @Anonymous 

What is the 'responsibilities ' column? and what is the logic for your expected output?

Could you please explain the logic for your desired result table, that will be a great help.

Regards,

Lin

[Anonymous]

Thank you for your response. The responsibility column is the product/region/team that each manager is in charge of and therefore get credit for in a sale. For example, Lindsay is in charge of Product B and Region Asia, therefore she should receive credit for any sale that occurs with either of these two categories involved. If there is a sale of Product B that occurs in America, she receives credit (due to Product B) and if there is a sale of Product A in Asia, she receives credit (due to Region Asia). However, if there is a sale of product A in America, she does not receive credit. Does that make sense?

[v-lili6-msft]

hi  @Anonymous 

For your case, you want use a or in relationship calculation, it doesn't achieve in one relationship.

So please try this way as below:

Step1:

Adjust the model relationship as below:

Step2:

Then create two measure as below:

Sales = 
var _region=VALUES('Product & Region'[Region They Manage])
var _product=VALUES('Product & Region'[Product They Manage])
var _employee=CALCULATETABLE(VALUES('Hierarchy'[Employee They Manage]),FILTER('Product & Region','Product & Region'[Team Filter]="Yes"))
return
CALCULATE(SUM(Sales[Price]),FILTER(Sales, Sales[Employee] in _employee ||Sales[Product] in _product ||Sales[Region] in _region))

responsibilities = 
var _region=VALUES('Product & Region'[Region They Manage])
var _product=VALUES('Product & Region'[Product They Manage])
var _table=UNION(_product,UNION(_region,ROW("value",CALCULATE(MAX('Product & Region'[Manager]))&"'s Team"))) 
return
CONCATENATEX(FILTER(_table,[Product They Manage]<>BLANK()),[Product They Manage],"&")

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Thanks again for the reply. I have a follow up question if you don't mind. If I wanted to change the logic in the filters to AND instead of OR I should just use && instead of ||, right? I thought this would be correct but I replaced the ||'s wiht &&'s in the measures you gave me and now the measure is showing up blank. Am i going about this the right way? Thank you  

[Anonymous]

This is working perfectly, thank you so much!

[mahoneypat]

I looked at the pbix file.  The problem is that the relationships are incorrect.  Delete the relationship that goes between the product and hierarchy tables.  Make a new one that goes between hierarcy and sales tables (1:Many on employee field).

That should fix your issue.