Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

     

    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
	)	
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     

    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
	)	
)
    • rbrabec's avatar
      rbrabec
      Regular Visitor
      6 years ago

      Hi Ross

      What would be your solution using a measure? I have difficulties finding a clear answer on how to use CALCULATE + any sort of function (SUM, AVERAGE etc.) for each different value in another column.

       

      Example: different locations (cities) and I want the SUM of the electricity usage for each location.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi rbrabec   Measures are all about the context they are used.  When you write a measure, you want to keep your mind on how you will be using the measure.

         

        For example, in this problem, where we want to calculate total sales for the given person, we might expect that we will be using the measure on a table, with 1 row per person.  In that case, its unlikely we would need to use a calculate statement, as the context would do all of the work.

         

        When you want to use Calculate with Sum, average etc, the easiest way to do it is to create a measure that handles the Sum/Average/Whatever, then in your 2nd measure you call it using your calculate statement.


        i.e:

        Sum Measure = SUM('YourTable'[YourColumn])

         

        Calculate Measure =  CALCULATE(
      [Sum Measure],
      'YourTable'[OtherColumn] = "Other Value"
)

         

        A Filter statement is often used in Calculate, but it doesn't have to be.  The above is a fairly simplisitic use case, but hopefully gives you a starting point.

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

    Could you please post me your desired result?

     

    Regards,

    Daniel He