Hi @defulamingge and welcome to the QuickSight community!
This is a fairly complex scenario that may depend on the data you have available within your dataset.
One quick question regarding your scenario; you mentioned that the sum of value percentages for Cat. A and Cat. B should be 100%…so ideally, your two different rating categories will combine to create 100% and you want to see what percentage each of the categories equal of that total per entity?
My initial thought is that you may be able to achieve by the following:
Set up a denseRank by a common field like Entity for example.
Then you can use that denseRank in the following to create 2 arbitrary groups based around the rank.
ifelse(maxOver({rank}, [], PRE_AGG)*(1/2) >= {rank}, "Category A", "Category B")
Last, you’ll want to utilize that in another calculated field that will provide you with the y-axis:
ifelse({Above Calc} = 'Category A', sumOver({value}, [{Category A}, {entity}], PRE_AGG)/sumOver({value}, [{entity}], PRE_AGG), {Above Calc} = 'Category B', sumOver({value}, [{Category B}, {entity}], PRE_AGG)/sumOver({value}, [{entity}], PRE_AGG), NULL)
Note that some of the fields utilized in this last calculated field will be dependent on how you name the first 2 and dependent on how fields are listed in your database.
Again, this may be dependent on your dataset and with the level of complexity, could be more beneficial if you were to upload a copy of your analysis with sample data to the QuickSight Arena view. That way we could potentially try out a few different scenarios and see how they respond for your case.