No Translation available yet
You have Slovak selected as language but this page has not been translated yet. Translate the page or view the content in the default space language below.
Display default
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;
}
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