Good day All,

I have been strugling lately to come up with a query that sum multiple columns and return extra 3 rows depending on what was selected.

For exapmle see my sample data below:

Town | Sector | Outside| Inside |Available|Price
Roy----Formal----0----------0---------1------0
Kobus--Formal----0 ---------0---------1------0
Wika---Formal----0----------0---------1------0
Mevo---Formal---1----------1---------1-------0
Hoch---Formal----1----------1---------1------1
Points------------2----------2---------5------1
Score------------10--------10--------10-----10
%score-----------20--------20--------50-----10


Each column has a constant weighting (which serves as a factor and it can change depending on the areas) in this case the weighting for this areas are the ones in the first row for the sector Formal:


Sector |Outside| Inside |Available|Price
Formal---1----------1 ------1-----1
Informal--1----------0 ------2-----1

I tried using the aggregate sum function in but it wont work since i need the factor in the other table. Which is where my challenge started

To compute the rows below the report
points = sum per column * weighting factor per column
Score = sum of no of shops visited (in this case its 5) * weighting factor per column
% score = points/Score * 100

The report should display as described above. With the new computed rows below.
I kindly ask anyone to assist me with this challenge as i tried searching on the internet for solutions but havent come across any.

Thanks a lot for your support in advance!!

Hi All,

No need to assist with this anymore, i found the solution.