View Full Version : CUBESET Formula that uses topcount and a slicer

2014-07-09, 01:17 PM
I have a cubeset formula that I am trying to use a slicer in but not sure of how to write. Here is my current formula (that works, but want to change the date cell to a slicer)

("topcount(({[Item Grouping - Item ID].[All].[Dining Room Chair].children,[Item Grouping - Item ID].[All].[Dining Tables].children}),20,sum(([Time Periods].[Descriptive Fiscal Periods].[Fiscal Day].&["&'Report Format'!$B$3&"]),[Measures].[Written Sales]))")

Formula works fine, but I want to change the cell location in B3, to be a slicer instead, such as:

("topcount(({[Item Grouping - Item ID].[All].[Dining Room Chair].children,[Item Grouping - Item ID].[All].[Dining Tables].children}),20,sum((slicer_time_periods),[Measures].[Written Sales]))")

Anyone who may have an idea? I have tried using the Time Periods .& and the the slicer to no avail, and also Time Periods ["&Slicer&"], but also does not work.

Bob Phillips
2014-07-09, 01:58 PM
I assume that SUM just won't address the slicer as it is not part of the cube, it is abstracting from the cube. If you want the sum of all of the elements in the slicer, could you not just sum that part of the cube? If you want to sum whatever the slicer has selected, I would think you would still have to go and retrieve those cub members, but that might be difficult as could be so many.

2014-07-09, 02:03 PM
That, and when I took the sum out and left only the measure, the data pulled only a segregated set of data, and did not change when I used the filter. The same top twenty items returned and did not return new ones of the date selected.

2014-07-09, 02:06 PM
I do use a slicer in the cubevalue function, and does pull in the correct sales number for the time selected in the slicer, but again, the items do not change, even if they are not the initial top twenty.

Bob Phillips
2014-07-09, 02:32 PM
Hang on, why are you using a SUM at all, surely the CUBEVALUE function will do the SUM for you, an you can embed the slicer as filter in formula.

2014-07-09, 03:22 PM
It is actually a formula that I used of the net. I am not all that familiar with these types of formulas and have been trying to find info on them, but needless to say, not much out there on them. I thought I would have to sum up the values for written sales per that time frame for the top count to grab the aggregated values for the items.

Bob Phillips
2014-07-09, 03:43 PM
I think we would need to see the workbook to properly help, it's a bit guessing in the dark at the moment. I assume you are querying SSAS here, not Power Pivot? If you could post the workbook, and direct us at the formulae and what you are trying to achieve, we might be able to offer solutions.

2014-07-09, 03:54 PM
I will have to scrub the data, but will hopefully post soon.