skip to Main Content


Yes, That IS a Measure

Third and Final Mini-Post for Today

Got a question from a friend yesterday that made me realize I should share this:  in PowerPivot, your formulas do NOT have to return numbers.  As I’ve shown several times, they can return dates, but they can also return just plain and simple text.

For instance, check out the formula for that Trend measure above:

[Trend] = IF([$ – Sales] > [$ – Sales YAG], “Up”, “Down”)

[$ – Sales] and [$ – Sales YAG] are both measures (“current sales” and “sales year ago.”)  So this IF() is 100% dynamic and responds to whatever fields and filtering/slicing you’ve done on your pivot!  If sales have gone up since last year, it returns “Up,” otherwise it returns “Down.”

One of those cool little tricks that comes in handy from time to time Smile

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 5 Comments
    1. Doh!

      Well, I’m an Excel-head at heart so maybe that’s ok. Then again I need images supported in Excel, which gives me Power View envy.

  1. Thank you Rob. This is rekindles my hope of creating a complicated text measure or calculated field. As finance analyst, I analyze rolling forecasts, budgets and actual for +100 services and tiers of services, +500 cost centers and +50 customers. I receive comments from other analysts for variance analysis.

    I want to build powerpivot report (e.g. prior forecast, new forecast and variance ) which shows comments based on filter context.

    I tried to put comments field in row area but it did not work (I don’t know why)

    I am thinking of creating a measure:
    Current vs Prior Comment:= if(Scenario=”Current vs Prior”, “current vs prior comment field”)

    Do you have any tip for solving such a problem? Thanks

    1. Well if it’s a measure, it can’t go on Rows. Measures can only go in the Values area. If you want something on Rows, it has to be a column (calculated column or “regular” column, either works, but it must be a column).

  2. Hi,

    Can text be returned from a measure on visuals like line chart or bar chart? It works on cards but can they work on line charts?


Leave a Comment or Question