Cubeset and CubeRankedMember interaction question

[DWig]

Hi all.

I have a set of GL accounts that I am using as the driver for my financial models - the first account is 1010. The GL description for this account is "petty cash".

There is also a set of GL descriptions, which I am trying to set up where they always show the proper description for the proper account. The first one in alphabetical order is "401k match". This comes up later.

My numerical account set is defined by =CUBESET("PowerPivot Data","[GLAccounts].[GLAccount_Code].children","Set of GL Accounts",5). This is in cell A2. This works properly.

My description set is defined by =CUBESET("PowerPivot Data","[GLAccounts].[Description].children","Set of GL Descriptions",1,"[GLAccounts].[GLAccount_Code]"). This is in cell A4.
From what I can tell, this means "Get a unique list of the GL descriptions, and sort it in ascending order by GL account code."

Looking for the first member of the numerical set, I enter =CUBERANKEDMEMBER("PowerPivot Data",A2,1). This displays 1010, which is correct.

Looking for the matching description for this GL account, which should be the 1st member of the GL description set (because it is supposedly sorted by account code), I enter =CUBERANKEDMEMBER("PowerPivot Data",A4,1). This displays "401k match", which is incorrect. It should be displaying "petty cash". Instead of displaying the value associated with the first GL account code (which I defined as the sorting variable) it is displaying the first alphabetical account description.

I have been trying different variations on this for the past three hours or so. I could copy and paste it into the back of my workbook and run lookups, but I'm trying to minimize file size, and these formulas will be duplicated well over ten thousand times.

I hope my explanation makes sense. I have found very little online in the way of troubleshooting the relationship between cuberankedmember and cubeset. I'm absolutely stumped.

Thanks in advance,
DWig

 

[powerpivotpro]

I think you need to define a measure/calc column that is something like MAX(Account Code) and use *that* as your sort value. Non-measure fields always sort inherently as text/alpha.

 

[DWig]

That looks like an interesting approach. I actually went with a different workaround which didn't require the sorting as I was using it previously. Thank you for the information though.

 

[miczusss]

Hi DWig

I have the exact same problem as you described. Could you share your solution please?

Thanks