I am struggling to create a summary measure that works with a tree model specified with left-right index pair.
I simplified the datamodel to demonstrate this: I have two tables, Task and Task item. Task table contain the hierarchy with ProjectID specifying the project and Left and Right specifying the range of TaskIDs that the row sums up for the project. For example row Subcategory 1 has Left 2 and Right 500: this means the row is summary of all rows with TaskID between 2 and 500. Task Item table contain the data to be summed with relation to Task table by ProjectTaskKey which is a combination of ProjectID and TaskID.
What I want is to show the data in a matrix table with sum of Sales from Task Item table on two different levels: a) by ProjectID and b) all TaskID levels with summary of the task and all tasks below it. Without measure I get the right sum on a task and ProjectID level but not on summary levels (Subcategories nor Total).
Here is were I got so far, but this is not working as it shows the total sum of all tasks: