Results 1 to 8 of 8

Thread: CUBESET Formula that uses topcount and a slicer

  1. #1
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0

    CUBESET Formula that uses topcount and a slicer



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

    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.

  2. #2
    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.

  3. #3
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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.

  4. #4
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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.

  5. #5
    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.

  6. #6
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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.

  7. #7
    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.

  8. #8
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    I will have to scrub the data, but will hopefully post soon.

Posting Permissions

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