Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
Check attached
@tresesco and @sunny_talwar ...
Adding two of the experts , who have given help to me earlier .
I am confused... what is the issue? You already have 240 coming as the partial sum for Bangalore... why do you need another row?
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 .
But what is the problem?
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 .
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?
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 .
Check attached