How to filter out multiple dimensions in Excel using cube functions?

Question

I've read this post (use Cube Functions to get filtered dimensions) and it's quite helpful, but I want to add one more level of filtering.

So let's say my data looks like this in PowerPivot:

Month     Category     Product #     Revenue
January   Trucks       00000001      $50000
January   Trucks       00000002      $75000
January   Cars         00000005      $45000
January   Cars         00000008      $90000
January   Trucks       00000003      $10000
February  Cars         00000005      $10000

So basically I have cars or trucks and I want to return the top selling 2 products in each category for the month of January, February, etc.

I can easily find the top selling products if I only have one dimension filtered. So I can find the top selling products (trucks OR cars) in January. I used the method in the link I provided above. But I want to ADD one layer to that and say, find only the top selling trucks in January.

How do I go about doing this? I was hoping I could use "nonempty" and just add each filtered dimension/condition as I please, but maybe I don't get how the syntax should be.

Answer 1

I've created a custom function that should satisfy your requirements, therefore avoiding complicated design & maintenance of multiple nested cube functions needed for multi-dimensional reporting of your vehicle sales data.

A further advantage is that using this method, multiple variations can be easily created & edited to provide additional functionality for future reporting needs.

Usage is similar to Excel's built-in Rank function:

• Call the function from a worksheet, specifying Month and/or Category, as well as the ranked result "rank number" that you need to have returned.

For example, to get the Top (#1) selling Truck for January, you could use formula:

=GetTopSeller ( "January", "Trucks", 1 )

or, to get the 10th best selling car for the month listed in cell A1, you could use formula:

=GetTopSeller ( A$1, "Cars", 10 )

---

The image below shows Syntax & Usage, as well as the sample dataset used in testing the function, and the example output based on the sample data*.

Option Explicit
'Written by ashleedawg@outlook.com for https://stackoverflow.com/q/47213812

Const returnForNoValue = "" 'could be Null, 0, "(Not Found)", etc

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

'Additional features which can be easily added on request if needed:
' add constants to specify Revenue or Product ID
' allow annual reporting
' allow list of vehicle types, months, etc
' make case insensitive


Public Function GetTopSeller(sMonth As String, sCategory As String, _
    sMonthRank As Integer) As Variant() '1=ProductID  2=Revenue

    Dim retData(1 To 2) As Variant

    strSQL = "Select Month, Category, [Product #], Revenue from [dataTable$] " & _
        "WHERE [Month]='" & sMonth & "' AND [Category]='" & sCategory & "' _
        Order by [Revenue] DESC"

    ' close Excel Table DB _before_ opening
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient

    ' open Excel Table as DB
    If cnn.State = adStateOpen Then cnn.Close
    cnn.ConnectionString = "Driver={Microsoft Excel Driver " &
        "(*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
        ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
    cnn.Open

    ' find appropriate data
    With rs
        .Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        .MoveFirst
        If (.RecordCount <= 0) Or (.RecordCount < sMonthRank) _
            Or (sMonthRank = 0) Then GoTo queryError

        'move the Nth item in list
       .Move (sMonthRank - 1)
        retData(1) = ![Product #]
        retData(2) = !Revenue
    End With

    'return  value to the user or cell
    GetTopSeller = retData
Exit Function

queryError:
    'error trapped, return no values
    retData(1) = returnForNoValue
    retData(2) = returnForNoValue
    GetTopSeller = retData

End Function

---

Below are instructions for copying the function into your workbook, thus making it accessible as a worksheet function. Alternatively, the sample workbook could be saved as an Add-on, and then accessed from any workbook by creating a reference to the Add-on.

---

How to add the VBA function to your workbook:

1. Select the VBA Code below, and hit Ctrl+C to copy.

2. In your Excel workbook, and hit Alt+F11 to open the VBA Editor (aka VBE).

3. Click the Insert menu in the VBE, and choose Module.

4. Hit Ctrl+V to paste the code.

5. Click the Debug menu in the VBE, and choose **Compile Project*. This checks the code for errors. Ideally "nothing" will happen, meaning it's error-free & good to go.

6. Close the VBE window by clicking the the " ✘ " in the top-right corner of the VBE.

7. Save the workbook. Your new function is now ready to use!

---

Usage of the function should be self-explanatory, but do not hesitate to contact me if you need a change, experience a problem, or have any questions.

Good Luck!