Calculate revenue using unit price times quantity and revenue percentage

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.

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.

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;

}