Basic calculated columns are not consistent

[VulcanPromance]

Hi.. Im currently reading this book about Dax to learn more indepth dax coding.. so while doing this I just created a basic excel table, and inserted it into to powerBI and the calculated 4 columns..

Its really basic.. 
Excel File

PowerBI Report

As you can see there are 4 calculated columns

SalesAmount = Sales[quantity]*sales[net price]

totalcost= Sales[quantity]*sales[unit cost]

grossmargin=sales[salesamount]-sales[totalcost]

grossmarginpct = sales[grossmargin]/sales[salesamount]

Now.. the fact that the total is wrong is OK.. thats a part of the chapter Im reading.. however.. the calculation of percent pr row is NOT consistent.. Why is the first row = 1 while all the others are 0.5 when 750/1500 clearly = 0.5

Can send files if required

[amitchandak]

@VulcanPromance , all other calculations is fine a new calculated column,

The last one should be done as a measure

grossmarginpct = divide(sales[grossmargin]),sum(sales[salesamount]))

[VulcanPromance]

That bothers me a great deal then.. Because that would mean the book is wrong since it clearly states its a calculated column and that it should calculate rows correctly. Do you have an explanation as to why it calculates only 1 row incorrectly? I know the best practice is using measures, but when trying to understand DAX I would need to understand why something goes wrong as well..

[v-yingjl · Accepted solution]

Hi @VulcanPromance ,

This is because visual will automatically display aggregated values in power bi, calculated columns themselves would not wrong.

For example, I have filtered the first SalesKey field 'Product A - 01.01.21 - 1.001' in the data view and found that there are two row fields about it:

Notice that the pct calculated columns are both 0.5 in the data view.

Since the calculation of your pct calculated column is essentially [Unit Cost] / [Net Price], so all the values should be 0.5.

But in visuals, power bi will automatically treat columns with the same ID as the same column and aggregate the values that can be aggregated:

As you can see,  'Product A - 01.01.21 - 1.001' field just appears only once in visuals but actually it has two rows in data view becasue power bi aggregate the values that can be aggregated automatically.

In conclusion, the calculated columns would not wrong. The solution to this problem is cancel one of the aggregated values in the visual or add add a column that can be clearly distinguished.

Hopes it could help you to understand it.

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

[VulcanPromance]

I cant believe I missed that duplicate.. thanks alot m8.. now it makes sense.