Measure have different result vs columns

[Nawaz]

I have a simple table consists of product price and quantity for each customer. I wanted to calculated total sales amount for each customer in two different ways just to explore the difference between calculated column and measure. Essentially, i would like to understand how and why the measure, SUM(QUANTITY) * SUM(PRICE) is 275 for Jack? ideally, it should be 135.

Thanks,

Nawaz

[Nawaz]

@rajendraongole1  thanks for the explaination.

I have a follow up question, when i did DISTINCTCOUNT(CUSTOMER), the measure returned 2 rows which is correct. Whereas, DISTINCTCOUNT(CUSTOMER) in the calculated column returns 8 rows for above dataset.
Can you please let me now whats the reason for such behaviour for same logic.

Thanks,

Nawaz

[rajendraongole1]

Hi @Nawaz - Measures are dynamic and calculated based on the context of the current filter or row in a report or visualization.it will return 2 because there are two unique customers (Jack and Pam). where as 

In calculated columns are computed for each row in the table at the time of data refresh, and they do not change dynamically based on filters in reports. When you add a calculated column using DISTINCTCOUNT(CUSTOMER), it is essentially calculating the distinct count for each row independently, which does not make logical sense in this context.

However, since calculated columns are evaluated row by row calculations.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1 · Accepted solution]

Hi @Nawaz We can do the calcualtion for total sales amount for each customer

The measure SUM('Table'[Quantity]) * SUM('Table'[Price]) is incorrect because it sums the quantities and prices separately and then again multiplies these totals. This approach does not respect the row context thats why you are getting 275 abnormal value.

calculated column to calculate Total sales: 

Using measure: Total sales sum:

With one single measure using sumx(iteration functions) you can create Total sales measure as follows: 

In report view:

Hope my explaination is clear. Thank you

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!