Struggling with filter context (link to PBIX)

[Anonymous]

I'm working on a KPI calculation, that needs to look at the averages achieved by other salespeople, then compare that to the "current" salesperson, and eventually average those across products.  I'm getting tied in knots, so put together a small representative example here:

https://drive.google.com/file/d/1Db04mnUCkqxVXpySnwVEgWHNE3qnBeLl/view?usp=sharing

I'm currently stuck on calculating the averages for the "others", as you'll see I've added notes to explain what I'm striving for.

Any help greatly appreciated.

[v-yalanwu-msft]

Hi, @Anonymous ;

Thank you for sharing, Could you please mark your post as Answered since it is working now?

It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Here was the DAX I ended up with in case it's of any use to anyone...

avCostForOthersByProd =

var u=min(People[UID])

var p=min(Products[ProductID])

--need to use relationship to limit to only selected users

var sa=CALCULATETABLE(allselected(salescost),USERELATIONSHIP(People[UID],SalesCost[SalesPerson]))

--filter the above to only show users that are the current user and product is the current product
var t=filter(filter((Sa),salesCost[SalesPerson]<>u),RELATED(Products[ProductID])=p)
--summarize the table including an average of COST this is where it broke previously, because I was evaluating a 
--measure and (not sure why) that didn't work.

var s=summarize(t,SalesCost[SalesPerson],Products[ProductID],"avu", average(SalesCost[Cost]))

--iterate over the summarized columns to get average for this Product OTHER users

RETURN
var a=CALCULATE(averagex(s,[avu]))

[Anonymous]

Thank you everyone for taking a look at this.  After spending some more time I made some changes and cracked it.  I went back to the previous model and was able to implement it there too with the same code (just referencing different tables).  Context Transition is a fickle beast it would appear.

[Anonymous · Accepted solution]

In case it's of any use to anyone, I only had to change one thing to make this work as needed.  Where I had created a measure :

AvCostByProdPerson = Calculate(average(SalesCost[Cost]),USERELATIONSHIP(Sales[SalesPerson],People[UID]))

...and then included that in a the measure "AvCostForOthersByProd".  

var t=summarize(r,Sales[SalesPerson],Sales[ProductID],"a",[AvCostByProdPerson])

...for reasons I'm not entirely clear on, this simply needed to be replaced without referencing the first measure:

var s=summarize(t,SalesCost[SalesPerson],Products[ProductID],"avu", average(SalesCost[Cost]))

My guess is that the context transfer in the "inner calculate" is being over-written by the outer one.

Thanks again for those that made suggestions, and I hope this helps someone at some point.

[m3tr01d]

After the model is done,
you can continue with your KPI.

If you want a measure to calculate the average of all the Others SalesPerson, you could try a measure like this.

--Store the current Sales person
VAR _CurrentSalesPerson = SELECTEDVALUE( People[Uname] )

--Calculate the average on all sales person different than the current one
RETURN
CALCULATE(
	AVERAGE( Sales_Final[Cost] ),
	People[Uname] <> _CurrentSalesPerson
)

But definitely start to correct your model first.

[Anonymous]

Not sure how selectedvalue will help here.  I've tidied the model as suggested, but still no further forward.
Here is the updated file with simpler model:
https://drive.google.com/file/d/1VYvRWdwcuQDyWVd2ycsYd-RwRawoWRQx/view?usp=sharing

thanks

[Anonymous]

Wrong link, this is the correct one...
https://drive.google.com/file/d/1XygYVmIfnh_kDWhZsZdnb534Vpe3tDYK/view?usp=sharing

[m3tr01d]

Hi @Anonymous ,

@AlexisOlson is right, you should merge the Sales and SalesCost Table. 
Use power query to merge the two table using Sales_Id and bring the column Cost and Salesperson in the Sales table. Then, you'll be able to reactivate your relationship with Person. The final model should look like this

[Anonymous]

Thanks
I've re-modelled as suggesed, you can see the new model here:

https://drive.google.com/file/d/1XygYVmIfnh_kDWhZsZdnb534Vpe3tDYK/view?usp=sharing

...but I can't have the SalesPerson>People relationship permanently active as there are other People linked to the Sales (e.g. the introducer, the approver, etc.).  
Any pointers appreciated.

[AlexisOlson]

Your model setup makes everything quite difficult. Having multiple fact tables in an incomplete 1-to-1 relationship is trouble waiting to happen and makes writing measures tricky.

If you can consolidate SalesCost and Sales into a single fact table so that your model is more like a standard star schema, then things are much simpler. With enough trial and error, I could probably eventually write a measure that works the way your model is currently but, assuming I were successful, it would be likely difficult to understand how exactly it works.

[Anonymous]

oops, wrong link, this is correct one:
https://drive.google.com/file/d/1XygYVmIfnh_kDWhZsZdnb534Vpe3tDYK/view?usp=sharing

[Anonymous]

Thanks for the suggestion/
I've updated the model as you've suggested, but still no nearer a solution.  You can see new model here:
https://drive.google.com/file/d/1VYvRWdwcuQDyWVd2ycsYd-RwRawoWRQx/view?usp=sharing
Any pointers appreciated.