when to use calculated columns vs measures for row dependent formulas (if/sumifs equivalent)

[Scottrick]

Hi, I'm pretty new to DAX but from what I've reading it sounds like most of what I build in my DataModel should not be done as a calculated field but instead as a measure to decrease file size and increase speed.

That said, I'm having a difficult time converting some formulas that work fine as a calculated column into measures, and I'm not sure if that's just due to my inexperience with DAX or if it's actually not possible to do so.

Here is a link to example formulas/use case in excel:

https://drive.google.com/drive/folders/1CCs6UItAkIDi8RIKN2Zpibr9CryAMkfm?usp=sharing

I outlined what would be direct feed into the table, what I have as measures, and what I currently have as calculated columns.  Appreciate if someone can help confirm if there is a way to convert the calculated columns to measures and still replicate the pivot table results.

Thanks so much!

[amitchandak]

@Scottrick ,  That is an excel sheet. Can share what formula you create in Power BI.

In case you do not need row-level calculation you can have formulas like.

Divide = divide(Sum(Table[Col1]),Sum(Table[Col2]))

sum =Sum(Table[Col1]) + Sum(Table[Col2])

diffrenece =Sum(Table[Col1]) - Sum(Table[Col2])

where you need row-level calculation

multiplcation =SumX(Table, Table[Col1]*Table[Col2])

Also check -

https://www.youtube.com/watch?v=My0bLn9voo4

https://www.youtube.com/watch?v=flpVwrtFD7o

[Scottrick]

Thanks Amit!  That helped me convert Calc1 into a measure. 

I've re-uploaded file but with the DAX parts: https://drive.google.com/drive/folders/1CCs6UItAkIDi8RIKN2Zpibr9CryAMkfm

Still havent been able to figure out how to convert the below calculated columns into measures:

Avg ASP1

% of Total Units1

Calc2 (running into a circular reference error even doing this as a calculated column)

Calc3

Appreciate your help!