skip to Main Content

When to Use Measures vs. Calc Columns

 
image

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.

Read the Rest