We recently encountered an issue where a calculated measure was not displaying a correct Total inside of the dashboard. The solution provided by Epicor/Phocas was to create a transform column in the design and do the calculation inside of the transform column, instead of doing the calculations inside of the measure. It works, but I’m still not really sure I understand why. Prior to this we have always been making calculated measures on their own.
Does anybody experiences with using a transform column vs making a calculated measure? Is one of these methods a best practice?
@MatthewWalsh ,
The difference between the calculated measure and transformed column is when the calculation is performed. The transformed column is calculated for each item in the database, while the calculated measure is done at the summary level and all components of the measure must be added as measures.
EG: If I have a database which has a separate row per line item of a sales and am interested in showing the sales tax, adding a calculated measure of “QtyPriceTaxRate” would require all 3 of these things to available as measures and the actual calulcation woould be “SUM(Qty)SUM(Price)SUM(TaxRate)" because the calculation is done against the summarization of the data. This will definitely result in incorrect results.
The correct thing to do in this case would be to add a transformed column titled “SalesTax” with the formula "QtyPriceTaxRate” and add that as a measure, so the calculations are done for each line item and then summarized.
Transformmed Columns also give you more freedom with your calculations.
Hope this makes sense and adds some clarity.
Scott