PDA

View Full Version : Multiple Member Expressions in CubeValue Formula



Charles Banks
2012-08-08, 07:38 PM
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

Ken Puls
2012-08-22, 07:10 AM
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.

Charles Banks
2012-08-22, 06:56 PM
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

Ken Puls
2012-08-22, 07:47 PM
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!)

:)

Bob Phillips
2012-08-23, 12:50 PM
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!

Ken Puls
2012-08-23, 05:04 PM
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.)

FergalK
2013-01-25, 03:20 PM
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!!

Roger
2013-03-19, 12:59 PM
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

Jon Acampora
2014-01-10, 12:58 AM
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.

FergalK
2014-01-10, 11:51 AM
Great stuff -thanks for the detail Jon!