In the straight table on the right I've included a green column showing what the result of the calculation show be should be. As for the pie chart, the one on the right has the correct values hard coded in and shows the results I'm trying to achieve.
Before anyone asks, these aggregations and Green/Yellow/Red statuses cannot be calculated in the load script because they have to be dinamically worked out depending on several other selections.
The problem is that in the real thing the data is much more granular so the sum(Sales) per Partner/Client is made up of several POs over different Quarters, which is why I'm trying to do the aggregation.
As for the distinct problem, in the real app I do have a DistinctPartner table where each equals 1 so that I can go sum(DistinctPartner) rather than count(distinct Partner).
The other reason why I need to keep the aggregation is that the pie chart doesn't have any dimension.