Calculate the sales per customer

[Anonymous]

Hi all,
I just wanted to calculate the sales per customer for each country.
Following are the measures I tried :
Sales/Customer = AVERAGEX(VALUES('Table'[Customer]),CALCULATE(SUM('Table'[Sales])))
SPC = AVERAGEX(VALUES('Table'[Customer]),SUM('Table'[Sales]))

First measures gives me the expected result. Can someone please tell me what went wrong with the 2nd one ?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

I made a sample file according to the formula you provided.

As shown in the screenshot,when you  apply  "CALCULATE(SUM('Table'[Sales]))" , the CALCULATE function modifies the filter context to evaluate the expression.So in "measure",the  average result is  applied for the whole table rather than single row in table.

For the concept of filter context, you can check this document.

https://docs.microsoft.com/en-us/dax/dax-overview#context

Best Regards,
Community Support Team _ Eason

[harshnathani]

Hi @Anonymous ,

Hope you have a Sales Table, Geography Table and Customer Table

and the relation is

Sales * --> 1 Geography

Sales* --> Customer

You just need a measure.

Total Sales = SUM(SalesTable[Sale])

Total Sales --> Measure

City is from Geography Table

Name from Customer Table

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , You have to give calculate or measure in x function.

There is a better article on it, not able to find

refer

https://powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/

https://www.decisivedata.net/blog/understanding-using-x-functions-dax

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi