Calculate over Total Column

[Anonymous]

Hello Guys,

Actually I'm working on a Matrix do compare the performance between my sales teams. In the actual scenario one client can be called by more than one team and today I have the following matrix.

CLIENT\TEAM	TEAM A	TEAM B	TEAM C	TOTAL CALLS
CLIENT JOSEPH	10 CALLS	5 CALLS	2 CALLS	17 CALLS
CLIENT MARK		10 CALLS		10 CALLS
CLIENT SUZY	5 CALLS	20 CALLS	50 CALLS	75 CALLS

Ok, now what I want to achieve is , considering the total Calls, and considering my matrix is showing data for a period of 1 year, I want to know the average number of calls a client has received, something like this:

CLIENT\TEAM	TEAM A	TEAM B	TEAM C	TOTAL CALLS	AVERAGE CALLS PER MONTH OVER A YEAR
CLIENT JOSEPH	15 CALLS	7 CALLS	2 CALLS	24 CALLS	2 CALLS
CLIENT MARK		10 CALLS		10 CALLS	0,83 CALLS
CLIENT SUZY	5 CALLS	20 CALLS	50 CALLS	75 CALLS	6,25 CALLS

What I dont know how to achieve is a way do display only the Average Calls for Total, up to know what I was able to do is to display the Average for each team and them it shows the total, but I don't want to display the average for every team, just for the total calls, could you please give some advice ?

thanks in advance

[v-xiaotang]

Hi @Anonymous 

Have you solved this question with lbendlin's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @lbendlin  , thanks for your reply.

The source is already cleaned up and you achieved the result by using 2 different matrixes , right ? I need to achieve it with only 1 matrix, is that posssible ?

thanks in advance

[lbendlin · Accepted solution]

" I need to achieve it with only 1 matrix" 

It is possible but it comes with a lot of work attached. You may want to reconsider that requirement.

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...

[lbendlin]

Let's clean up the source data first

unpivot

cleanup

Once you have your data in good shape the rest is easy.

see attached pbix