There are times that the data set only contains the unit price and sold quantity without the calculated revenue. This tutorial will show you how to calculate the revenue and revenue percentage towards total revenue.

It is recommended to get familiar with memberSum function before proceeding with this tutorial.

The table we are going to build will have two drill down levels, Product Group and Product Name. Here is how the table will look like.

Make sure you are displaying the Unit Price correctly. The members aggregation should be Average rather than Sum.

The Revenue, which equals to Unit Price times Quantity is calculated using membersSum,

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
    int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue + thisRevenue;     
}

return totalRevenue;

Make sure you place the order of the drill down levels in the parameters of membersSum correctly. It should follow the order of the drill downs in the table, so Product Group first and then Product Name. The last drill down level Transaction ID will make sure it aggregates total revenue for all transactions belongs to the same product group and same product.

In order to calculate the revenue percentage, we need to calculate the total revenue first. 

aggregatePrevLevel(2){ 
int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
    int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue;
}

So the code for calculating the revenue percentage is as below:

int total = aggregatePrevLevel(2){ 

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
	int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue;
}

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
	int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue/total;

Next Steps