Home Forums Power Pivot Dynamic name for a measure

This topic contains 13 replies, has 2 voices, and was last updated by  Gary C 2 years, 2 months ago.

Viewing 14 posts - 1 through 14 (of 14 total)
  • Author
    Posts
  • #7186

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    Hello
    I am relatively new to PowerPivot for Excel and have just bought a number of books on the subject which I’m slowly working my way through.

    I’ve got some data which I’ve created a number of measures for, which represent totals for the current month, and previous 3 months. At the moment, the measure names are currentmonth, monthminus1, monthminus2, and monthminus3. the calculations are fine, but I’d prefer it if the measure names could be replaced by the actual month names as headings in the powerpivot, and dynamically update accordingly when a new month begins.

    e.g. January instead of currentmonth
    December instead of monthminus1
    november instead of monthminus2

    and then when February starts, show
    February instead of currentmonth
    January instead of monthminus1
    etc.

    Is this possible? If so, could somebody explain in relatively simple terms how I can begin to achieve this?

    Thanks for your time,
    Gary

    #7188

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Gary,

    If you are trying to put measures into column or row drop zones, the answer will be “you cannot do that”.

    However, you may have options outside of a pivot.

    What version of Excel are you using?

    Tom

    #7189

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    Hi Tom,

    I’m using Excel 2013. The only way I can get around it at the moment is to have a row above the PowerPivot column headings that use a vlookup to find the corresponding Month name from another table.

    For tidyness, I would have rather had the column (measure) names change dynamically, but I guess that this isn’t possible.

    Gary

    #7190

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Gary,

    Since you are using Excel 2013, there are a couple of options outside of a regular pivot table that are both queries from the Excel side against the Power Pivot model.  One of the query options returns individual values, the other option returns a table.

    Would you like to explore these options? If so, could you attach a workbook with some sample anonymized Power Pivot data? It would help at least to see an exact picture of what you want your output to look like (I want to make sure that what you want is what I currently visualize).

    Tom

     

    #7192

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    That’s extremely kind of you to offer so much assistance.

    The attached screenshot shows an example of the kind of layout I’m currently creating with the powerpivot with the headings in the first column changed to generic values.

    The headings with a blue background are the measure names currently being picked up, and the text in red shows the values I would like to see displayed in row 4 instead of the measure names. At the start of a new month, I want the headings in row 4 to update, so the heading in column B would be Nov-16… column E would be Feb-17 etc.

    If you’d rather me attach a workbook rather than a screenshot, I can create one if necessary.

    Thanks again,
    Gary

    Attachments:
    You must be logged in to view attached files.
    #7196

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    Sorry – in the above post, I should have said headings in row 3, not row 4!

     

    #7197

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Gary,

    Have taken a look at the image.  What makes this an advanced topic is that across the top of the pivot the columns do not belong to a common dimension (as does the Categories along the rows).

    However,  I believe it would be possible using cube formulas, which Rob discusses in the following links:

    Since this would fall under an advanced topic, you might be ahead of the time-invested-into-Power-Pivot-versus-returns game if you waited until you have more experience.  If you would like to do some investigation now anyways, please send a sample workbook with anonymized data if you would like me to help out.  The data can be totally fictitious, but the model and measures should be consistent with your production workbook.
    Tom
    #7198

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Gary,

    Have taken a look at the image.  What makes this an advanced topic is that across the top of the pivot the columns do not belong to a common dimension (as does the Categories along the rows).

    However,  I believe it would be possible using cube formulas, which Rob discusses in the following links:

    Since this would fall under an advanced topic, you might be ahead of the time-invested-into-Power-Pivot-versus-returns game if you waited until you have more experience.  If you would like to do some investigation now anyways, please send a sample workbook with anonymized data if you would like me to help out.  The data can be totally fictitious, but the model and measures should be consistent with your production workbook.
    Tom
    #7203

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    Hi Tom,
    Thank you for the links you posted – although I found them a little confusing at first (due to my newness to PowerPivots), I have successfully managed to create cube formulas that appear to solve my problem.
    If they update automatically when I link it to data for February then I will be overjoyed!
    I think this technique may be useful for other areas too… it looks like I have some more reading and practising to do now!
    Thank you so much for all your help.
    Gary

    #7205

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    I have one more question if you don’t mind, relating to the cube formulas…

    If I have a powerpivot that has columns for days of the current month with a total in the rightmost column, I guess I could use a cube formula to add another calculation at the far right hand side (see graphic 1)?
    Assuming this is correct, when I refresh my data the following day, an extra column will be needed to display the appropriate data for the latest day in the month. Will this extra column automatically appear if the powerpivot has been converted to cube formulas, and would the additional calculation added still appear at the right hand side (see graphic 2), or would this be automatically overwritten (see graphic 3)?

    Apologies if this should have been posted in a separate thread, but as the responses above related to cube formulas, I thought it might still be appropriate to add it here.

    Attachments:
    You must be logged in to view attached files.
    #7217

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Gary,

    A downside to cube formulas reports is that their shape is static, even when they are created from a pivot table.

    Other options to explore would in building reports would be (1) Power Query and (2) DAX queries from the Excel side using EVALUATE.  If interested in learning more about EVALUATE, let me know.

    As of this date, the best resource that I have found to get a handle on Power Query (Get & Transform in Excel 2016) is the book, “M is for (Data) Monkey” by Ken Puls and Miguel Escobar.

    Tom

     

    #7218

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    Hi Tom,
    Thanks once again for your reply. I have the “M is for (Data) Monkey” book, so I can take a look at that, but if you can point me in the right direction for resources to learn about EVALUATE in DAX then that would be helpful. I’ve just taken a brief look online for it by doing a Google search, but from my initial look, I can’t understand it’s purpose, and particularly how it might be helpful in relation to my above query.
    You have already spent a significant amount of time responding to my above posts, so if it’s too much trouble to help regarding EVALUATE, I can try doing a bit more research myself.
    Thank you.

    #7219

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    An excellent resource for learning about EVALUATE and DAX queries is Chapter 14 of Building Excel Models with Power Pivot by Marco Russo and Alberto Ferrari.

    Like cube formulas, sometimes a DAX query (EVALUATE) or Power Query will deliver a solution when a pivot table does not.

    Tom

     

    #7221

    Gary C
    Participant
    • Started: 7
    • Replies: 14
    • Total: 21

    Looks like that’s another book I’ll have to add to my shopping list.  Thanks again for your help and advice – much appreciated.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.