Mike_Alex
New member
I have been struggling with this calc for a while now, turning it around and sorting it anyway I can, but cannot seem to get straight. May be a bit of a challenge, and am hoping the experts on this site may be able to resolve, know you are very good at what you do.
I am using an OLAP cube for a data source, and am trying to get a topcount that will not return any empty values. Here are the calcs that I am running:
Measure would be: =CUBEMEMBER("OLAP Cube","[Measures].[Sales]")
Row would be at: =CUBEMEMBER("OLAP Cube","[Store Location].[My Store]")
For the measure, I want to sort by top sales by item groupings, so I use a top count formula (Excel will not take the entire formula so put into text for Excel to understand the MDX part): =("topcount(([Retail Master].[Item Grouping].children),10,sum(([Time Periods].[Fiscal Year].[All].[2014]),[Measures].[Sales]))")
In turn, I put this into a cubeset formula to pull in the value: =CUBESET("OLAP Cube",(cell value that holds the topcount formula),"Cubeset")
This all works fine, and I use the formula =CUBERANKEDMEMBER("OLAP Cube",(cell that holds my cubeset formula),ROW(A1))
For the value, it is =CUBEVALUE("OLAP Cube",(cell that holds the store location),(cell that holds my measure which is a cubemember formula with the measure),(cell that holds the item grouping in the row, or the cuberankedmember returned value).
The problem with the formula is when it returns the data, there is one member that reads =CUBEMEMBER("OLAP Cube ","[Retail Master].[Item Grouping].&[]") and it returns as a ranked member.
So, here is the question, can I incorporate a nonempty into the text formula (or MDX segment) so that it will skip over this value and not pull in the empty?