I've come across an confusion about table calculation when showing a table view.
As you can see, there are different level of detail in the data source (pic.1) : every saler belongs to no less than one subgroup and parent group as well as different departments, and the column SubGroupTotalSales is already a sum value for each subgroup in each individual month.
What I want to see in the view is the value sum, sales sum and a rank by value sum for each ParentGroup for each month (pic.2). The displaying order is determined by the latest rank. However, the value sum is quite straight forward to calculate while the sales value need to be limited to be count once for each subgroup to avoid duplication. I suppose cal. fields for the rank and sales total are necessary to create but the table calculation should be set to different level. That's what I've done and the issue is when I try to create a filter to count in only one record for each subgroup to show the correct total sales the value sum number turns out to be overlapped somehow. Moreover, the desktop sometimes cannot respond well probably because my attempts made the table calculation too complex? I wonder if I've done this kind of calculation in a proper way or could you give any guidance to make it work. That would be super appreciated.