This post will introduce the Excel CUBEKPIMEMBER function. The CUBEKPIMEMBER function in Excel is a powerful tool for working with Key Performance Indicator (KPI) data in OLAP cubes. This function allows you to retrieve information about a KPI in a cube, such as its status, value, and goal. Whether you are creating reports or performing data analysis, the CUBEKPIMEMBER function is an essential tool for working with KPI data in Excel.
How To Use CUBEKPIMEMBER Function
The CUBEKPIMEMBER function in Excel is used to return a Key Performance Indicator (KPI) member in a cube based on its name. A KPI is a metric used to evaluate an organization’s success in achieving its goals.
The syntax for the CUBEKPIMEMBER function is as follows:
- connection is a required argument that specifies the connection to the cube data. The connection string should be entered as a text string in double quotes.
- kpi_member_name is a required argument that specifies the name of the KPI member to be returned. This argument should also be entered as a text string in double quotes.
The CUBEKPIMEMBER Function Example
Let’s take a look at some examples to help illustrate how to use the CUBEKPIMEMBER function:
Suppose you have an OLAP cube named ”
ThisWorkbookDataModel” that contains a KPI named ”
Sum of Sales 2“.
To retrieve the KPI member for this KPI, you can use the following formula:
=CUBEKPIMEMBER("ThisWorkbookDataModel","Sum of Sales 2",1,"TotalSalesValue")
Before executing the above formula, you need to add the data from this table to the data source as the workbook data model and create a new KPI to add your data model.
This formula will return the KPI member for the ”
Sum of Sales 2” KPI in the ”
ThisWorkbookDataModel ” data source.
You can use the CUBEVALUE function in Excel to get the corresponding KPI value, the formula is as below:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Sales 2]")
- The CUBEKPIMEMBER function only works with OLAP cube data sources, so make sure that the data source you are using is indeed an OLAP cube and that the KPI you want to retrieve is defined in the cube.
- If the
connectionargument or the
kpi_member_nameargument is incorrect, or if the KPI member you want to retrieve is not defined in the cube, the CUBEKPIMEMBER function will return an error.
- The returned KPI member text string can be used in further calculations or displayed in a cell.
- The CUBEKPIMEMBER function is available in Excel 2013 and later versions.
The CUBEKPIMEMBER function in Excel is a valuable tool for working with Key Performance Indicator (KPI) data in OLAP cubes. With its flexibility and functionality, the CUBEKPIMEMBER function can greatly simplify the process of working with KPI data in Excel. Whether you are a business analyst or a data professional, mastering the use of the CUBEKPIMEMBER function is an essential step in making the most of your OLAP cube data.