I attended yesterday’s DAX Masterclass session. I have two questions based on the content.
During the class, Sam added a column to the Sales table to demonstrate how to add a column to a table. The table was Total Costs (Quantity * Unit Cost). Later he deleted the column saying it wasn’t necessary because Quantity and Cost were on the Sales table and could be used to obtain the Total Costs. I noticed that Total Revenue (Quantity * Unit Price) was on the Sales table, also. Should Total Revenue been deleted, also?
I’ve observed several Edna training classes and showcases, and downloaded the PBIX files for several of them. I’ve noticed that in some instances, Unit Price and Unit Cost reside on the Products table, in other cases they reside on the Sales table. Is there any reason for the difference? Froma Best Practices perspective, that is.
For first question, the answer is - “Yes”. Yes, you can absolutely delete that “Total Revenue” column from the table or data model. Since you can achieve those same results by writing the simple formula or measure i.e.
Total Revenue/Sales =
SUMX(
Sales ,
Sales[Quantity] * Sales[Unit Cost]
Also the reason why I say - “Yes” to delete that particular column is because when you remove un-necessary columns from the data model which are of no use it’ll help in further optimize the model and will reduce the size of it. So I guess, that answers your first question now.
For second question, yes you might observe in certain instances that Unit Price and Unit Cost reside in the Products table. This actually nothing has to do with the best practices to be honest. This is done to showcase that in real life scenario you can have this columns either in the Sales or Fact table or you can also have this information in the Products or Dimension table. And therefore, how you can calculate the results based on the different scenarios. So I guess, that answers your second question as well now.
Hoping that you find this useful and clears your understanding on the video that you saw today.
