The CUBEKPIMEMBER function in Excel is used to return a key performance indicator (KPI) name, property, or measure from a data model (typically in a cube or data source like SQL Server Analysis Services). Here’s a step-by-step guide on how to use this function:
Syntax
CUBEKPIMEMBER(connection, kpi_name, kpi_property, )
Parameters
- `connection`: A text string defining the name of the connection to the data model or cube where the KPI data is located. This should be a valid connection name you’ve set up in Excel.
- `kpi_name`: A text string indicating the name of the KPI.
- `kpi_property`: Specifies which part of the KPI to return. It should be a numerical value representing:
- 1: KPI Value
- 2: KPI Goal
- 3: Status
- 4: Trend
- 5: Weight
- ``: This is an optional parameter where you can specify a text string that will display as the label for the KPI in your report or dashboard.
Steps to Use
=CUBEKPIMEMBER("YourConnectionName", "Sales Growth KPI", 1)
- Set Up Your Connection: Before using CUBEKPIMEMBER, ensure that you have a proper connection setup in Excel linking to the external data source, like a SQL Server Analysis Services cube.
- Identify Your KPI: Know the KPI name exactly as it is defined in your cube or data source.
- Determine the KPI Property: Decide which part of the KPI you need to retrieve (e.g., value, goal, status).
- Enter the Function:
- Click in the cell where you want to display the KPI result.
- Enter the formula using the syntax described above.
- For example:
- Replace `”YourConnectionName”` with the name of your established connection, and `”Sales Growth KPI”` with the exact name of your KPI. Use 1, 2, 3, 4, or 5 for the `kpi_property` to retrieve the respective part of the KPI.
- Result: Once you press Enter, Excel will query the data model or cube, and display the specified part of the KPI in the chosen cell.
Example
Suppose you want to retrieve the goal of a KPI named “Revenue Target” from your data model and you’ve named your data model connection as “SalesDataModel”. You would use:
=CUBEKPIMEMBER("SalesDataModel", "Revenue Target", 2)
This will display the KPI goal for “Revenue Target” in your worksheet.
Tips
- Ensure that your data connection is working; you might need to refresh data connections or reinstall if issues arise.
- Use the Excel “Data” tab to manage existing relationships and connections for optimal data model interactions.
- KPIs are generally defined by more advanced users, make sure you have access to the correct data and permissions.
This function is particularly useful for creating dynamic and sophisticated dashboards within Excel, allowing real-time updates reflecting KPI changes.
Unlock Your Potential
Excel
Access
Power BI
