Sales by Agent, team and supervisor

[Anonymous]

so I can't seem to get this to work 

https://ufile.io/k8h1y6r0

I'm trying to show all my agents sales, then show the avg sales per Supervior (region) and Team in individual columns. 

Avg Sales by Supervisor = CALCULATE(

AVERAGE(Data[sales])

, ALLEXCEPT(Data,Data[Supervisor])

)

works if the Supervior is visable on the matrix, but it I don't want to display supervior how can I get this to work?

when I remove the supervisor from the visual then it seems to revert to an avg of the total which I don't want. 

for Agent Bill for example I would ideally see

Agent - Sales - Avg by Supervior - Avg by Team

Bill - 70 - 93 - 246

same with Avg Sales by Team. 

[amitchandak · Accepted solution]

@Anonymous , Try like

averageX(values(Data[Supervisor]), calculate(AVERAGE(Data[sales]), ALLEXCEPT(Data,Data[Supervisor])))

or

averageX(summarize(Data, Data[Supervisor], "_1", AVERAGE(Data[sales])), [_1])

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

looks like this method also works! thanks

[Ashish_Mathur · Accepted solution]

You are welcome.  If my reply helped, please mark it as Answer.

[amitchandak · Accepted solution]

@Anonymous , Try like

averageX(values(Data[Supervisor]), calculate(AVERAGE(Data[sales]), ALLEXCEPT(Data,Data[Supervisor])))

or

averageX(summarize(Data, Data[Supervisor], "_1", AVERAGE(Data[sales])), [_1])