Complex calculations (groupBy and ALL) in QlikSense

[QS21]

Hi everyone!

I am dealing with some logics that are easy in excel but are really breaking my head in Qliksense.

I have a table with Variables as follows:

MonthYear variable which has only few months of data, Quarter(I dont have, need to derive in QS),Catgory, Group, Bal1 and Bal2 

Table 1 below sum is at group by quarter level and table 2, we are taking average of table 1. but bal2 is only fir category not at Group level whereas bal1 is at both category and group level. 

Table 3 has dummy numbers created based on table2

TABLE 4 IS WHAT I NEED!!

CHART below is what I need to draw in Qlik Sense :

I would like to calculate this percentages (table4) in Qliksense and show them in a line chart where X-Axis will be GROUP and lines are CATEGORIES..  could someone help me please!  (all the sum and averages are at Quarter level)

NOTE: the above tables are only a small part of my master table, I have extracted this piece to explain the logic I need. Thanks!

attached the excel file with dummy data

Thanks a lot!