Screwed granularity , a measure needs to be conditionally aggregated and super manipulation needed

[prahlad_infy]

Hi All ,

I have a wired data  where a measure needs to be conditionally aggregated based on dimentionality and super manipulation of Total required in order to maintain correct Rating . 
Explaining through example : My source data is something like this , where at source itself data is aggregated and present as rows due to overall rating considerations .

In above pic ,  Sq_Value of first record is actually aggregation of Sq_Value from second and third record ; but i overall rating is 

individual rating , hence cant be aggregated .

I want to achieve  a tabulation which is below on right side (Totals at right place , be it through script or expression)

But partial sums are adding already aggregated values 

 

Not sure how to represent whole data where correct aggregation is retained with correct ranking . Guess this is too much for my level . Please suggest .

Thank you .

Source 

LOAD * INLINE [

City, AD1, AD2, AD3, Sq_Value, Overall_Rating
Chennai, Cant, Area A, , 60, 3
Chennai, Cant, Area A, Plot 1, 20, 8
Chennai, Cant, Area A, Plot 2, 40, 7
Chennai, Cant, Area B, Plot 1, 75, 6
Chennai, Cant, Area B, Plot 2, 25, 6
Chennai, Cant, Area B, Plot 3, 100, 3
Chennai, Cant, Area B, , 200, 8
Chennai, ECR, ECR Shore 1, , 25, 10
Chennai, ECR, ECR Shore 2, , 25, 7
Chennai, ECR, , , 50, 8
Chennai, , , , 260, 6
Hyderabad, , , , 100, 6
Bangalore, Bangalore East, , , 190, 8
Bangalore, Bangalore West, , , 50, 8
Mangalore , Mangalore East, , , 30, 4
Mangalore , Mangalore West, , , 55, 6
Bangalore, , , , 240, 3
Mangalore , , , , 85, 6
South, , , , 685, 8
];

[prahlad_infy]

@tresesco  and @sunny_talwar  ... 

Adding two of the experts , who have given help to me earlier . 

[sunny_talwar]

I am confused... what is the issue? You already have 240 coming as the partial sum for Bangalore... why do you need another row?

[prahlad_infy]

Hi Sunny ,

Actually data at source level itself has three rows , where one of the row has already aggregated data . And dat is actually causing the problem . 

[sunny_talwar]

But what is the problem?

[prahlad_infy]

[prahlad_infy]

Sorry for leading to confusion . 

 The problem is when i show that in pivot table my total are getting wrong ...

Actually the Total should come as 240 , instead of 480 . 

[sunny_talwar]

But isn't the 240 showed in the first row already your total? You want to show 2 totals? That would be confusing to the users?

[prahlad_infy]

Yes Exactly , that is where i needed some sort of marker or highlight that the first row is actually total of next two rows . 

And precisely this is the reason i needed Total manipulation like table on right side . 

 

[sunny_talwar · Accepted solution]

Check attached