cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jarodjp42 Regular Visitor
Regular Visitor

SUM value by category

 I am trying to take the following data and calculate the values for each person.

 

personsalesAverage
Jerry3010
Jerry3010
Jerry3010
Tom2512.5
Tom2512.5
James2010
James2010
Joe2525

 

I am looking to take the total sales for each person and create a new column (average) that will average the total sales and break it out for each line. Can someone explain how this can be done? Thank you in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUM value by category

 

I don't agree that this is the right way to do this, using measures you can calculate this information display it without needing to store the value on every single line in your data set (which is what a calculated column will do).  Never the less, this is the calculated column code to do what you are asking:

This will produce your calculated column for sales

Total Sales = var thisPerson = [person]
RETURN
CALCULATE(
	SUM('YourTable'[sales])
	ALL('YourTable'),
	'YourTable'[person] = thisPerson
)


This calculates the average

Total Average = var thisPerson = [person]
RETURN
DIVIDE(
	[Total Sales],
	CALCULATE(
		COUNTROWS('YourTable')
		ALL('YourTable'),
		'YourTable'[person] = thisPerson
	)	
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


2 REPLIES 2
Super User
Super User

Re: SUM value by category

 

I don't agree that this is the right way to do this, using measures you can calculate this information display it without needing to store the value on every single line in your data set (which is what a calculated column will do).  Never the less, this is the calculated column code to do what you are asking:

This will produce your calculated column for sales

Total Sales = var thisPerson = [person]
RETURN
CALCULATE(
	SUM('YourTable'[sales])
	ALL('YourTable'),
	'YourTable'[person] = thisPerson
)


This calculates the average

Total Average = var thisPerson = [person]
RETURN
DIVIDE(
	[Total Sales],
	CALCULATE(
		COUNTROWS('YourTable')
		ALL('YourTable'),
		'YourTable'[person] = thisPerson
	)	
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Community Support Team
Community Support Team

Re: SUM value by category

Hi @jarodjp42,

Could you please post me your desired result?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.