Results 1 to 10 of 10

Thread: Multiple Member Expressions in CubeValue Formula

  1. #1

    Multiple Member Expressions in CubeValue Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    What I am trying to do is simplify the formula for multiple member expressions(?) in the CubeValue function. I'm fairly new to Powerpivot so please pardon if I have used the wrong terminology in my title or my examples.

    I currently have the following function working and returning the correct results.

    Single Selection
    • =CUBEVALUE("PowerPivot Data","[Sum of NetSales]","[xStoreID].[1]","[FiscalPeriod].["&E$56&"]","[FiscalWeek].["&E$57&"]")


    What I would like to do is be able to return results from multiple stores without having to use something like the following. Primarily because there are quite a few stores in the list and long formulas are more difficult to troubleshoot and I need to perform the same process for a time component.
    =CUBEVALUE("PowerPivot Data","[Sum of NetSales]","[xStoreID].[1]","[FiscalPeriod].["&E$56&"]","[FiscalWeek].["&E$57&"]")
    +CUBEVALUE("PowerPivot Data","[Sum of NetSales]","[xStoreID].[4]","[FiscalPeriod].["&E$56&"]","[FiscalWeek].["&E$57&"]")

    Shouldn't something like the following work.

    • =CUBEVALUE("PowerPivot Data","[Sum of NetSales]","[xStoreID].[1]","[StoreID].[2]","[FiscalPeriod].["&E$56&"]","[FiscalWeek].["&E$57&"]")


    I'm hoping that I am just missing something from a formatting standpoint that would allow me to select multiple items for the same expression.

    Thanks in advance for your time.
    Charles

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    I may also blow this up a bit, but my understanding of the CUBE formulas is that they are intended to essentially drill down into your data based on the additional stipulations you feed them. So end result is that you can drill down to "Sum of NetSales" for a FiscalPeriod and FiscalWeek. At that point, throwing a store on it filters down to that store. I'm not aware of a way to filter to multiple stores though...

    You may find this a bit helpful though... you can also break down your CUBEVALUE formula and nest CUBEMEMBERs inside it:

    =CUBEVALUE("PowerPivot Data",
    CUBEMEMBER(
    "PowerPivot Data","[Sum of NetSales]"),
    CUBEMEMBER(
    "PowerPivot Data","[FiscalPeriod].["&E$56&"]"),
    CUBEMEMBER(
    "PowerPivot Data","[FiscalWeek].["&E$57&"]"),
    CUBEMEMBER(
    "PowerPivot Data","[xStoreID].[1]"))

    Arguably, this can make it a bit easier to read as it encapsulates the filters a little more obviously.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thanks for the response Ken.
    Getting your message made me look at this again with a set of "fresh eyes" and I think I may have found a solution to what I was attempting to accomplish. In case you are interested the following is a summary of how I got this working.

    1) Created a pivot table and installed some slicers to allow me toggle different levels of aggregations.
    2) Used OLAP Tools --> Convert to formulas from the Pivot Table --> Options ribbon and used that as a starting point to understand how the slicers impacted the cubevalue formulas.
    3) Using the formula that I had started with in my original question I replaced "[xStoreID].[1]","[StoreID].[2]", with Slicer_xStoreID so my formula now looks like the following:

    =CUBEVALUE("PowerPivot Data","[Sum of NetSales]",Slicer_xStoreID,"[FiscalPeriod].["&E$56&"]","[FiscalWeek].["&E$57&"]")

    4) Now when I make changes to the slicer the formula updates the data appropriately.

    Thanks again for your continued assistance

    Charles

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Nice! That should work, yes. (It's been too long since I was playing in the PowerPivot OLAP space, and I forgot you could link the Slicers in there!)

    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    I looked at this a while back and spent ages trying to make it work, and it was staring me in the face all of the time. DOH!

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    As I recall, the first time I tried to do this I spent a ton of time trying to figure out how to return my slicer values to a cell so that I could feed them into an OLAP formula. (But I was always after a single value, not multiples.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Cool!!
    Thanks a million for this post - it has saved me a huge amount of effort and REALLY helps my solution - you guys are great!!

  8. #8

    How to recreate in formula the filtering provided by a slicer

    The above helps a lot, but I would like to be able to reproduce the filtering the slicer does in the formula without having to use a slicer. Maybe I am being too picky? :P

  9. #9
    Hi Roger,
    You can use a CUBESET function instead of using a slicer. CUBESET can handle multiple expressions so you can filter down to specific members in a field, provided they are associated in a group or hierarchy. You can even use CUBESET to return a range of dates and then reference it in a CUBEVALUE formula to sum a measure based on a date range. For example, the following CUBESET formula will return a set of dates between two periods.

    =CUBESET("PowerPivot Data","[qOrderLog].[Date Closed].[2013-10-01]:[qOrderLog].[Date Closed].[2013-10-31]","Oct Close Dates Set")

    You could then reference the cell that contains this formula in a CUBEVALUE formula to return the sum of a measure for that date range. One important thing to note is the dates must be in the format: YYYY-MM-DD. You can use the TEXT function to convert them.

    I hope this helps. Let me know if you have questions.
    Last edited by Jon Acampora; 2014-01-10 at 01:01 AM.

  10. #10
    Great stuff -thanks for the detail Jon!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •