skip to Main Content

 
image

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 founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

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?

    Thanks
    Kamal

Leave a Comment or Question