Paper or Plastic: Measure, or Calc Column?
“What? I’ve Never Written This Post?”
<pause while I search the archives> “No??? I Haven’t??? Sheesh.”
It’s such a fundamental question. When you need to write a PowerPivot formula, which kind should you write – a calculated column or a measure (now renamed “calculated field” in 2013)?
Well, there is no perfect 100% answer to this question. But there are a handful of crucial differences and guidelines that make your choice clear 95% of the time. And the other 5% generally don’t matter.
So let’s get to those guidelines and differences, shall we?
Guideline #1 – When a desired number is a fixed property of a single row, calc columns are usually appropriate.
Here’s a common example. You have a Sales table, in which every row is a separate transaction. The table has [ProductCost] and [SalesAmount] columns and you want to know how much Margin (Profit) is made on each transaction.