I am doing sales results in qlikview and would like to calculate the total percentage in a line, using pivot table. For each product, I calculate the 'net revenue' , 'gross profit' and divinding gross profit per NET REVENUE we have the GROSS MARGIN. This is only for one product, but there is a problem for obtaining the GROSS MARGIN for more than one in a pivot table, as I am using SUM() to have total per month.
What I would like to do is even the user selecting one or more product, the GROSS MARGIN would be the result of division (GROSS PROFIT/(NET REVENUE)).
Please see table below with desirable result:
ID PRODUCT GROSS PROFIT NET REVENUE GROSS MARGIN MONTH
1 pineapple 100 10 (100/10) = 1000% JANUARY
2 apple 50 40 (50/40) = 80% JANUARY
MONTH GROSS PROFIT NET REVENUE GROSS MARGIN
JANUARY 150 50 (150/50) = 300%
IF I use the sum() it will return the value of 1080% and not the correct (300%).