In summary, my problem is that I have a database with a few categories, and I need to use table calculations for each category (running_sum in my case), and finally combine all categories together in a calculation field using case statement. The problem is that I cannot combine them in case statement since it cannot mix aggregated and non-aggregated data!
The attached file is a very simplified version of my file. Suppose I would like to show "WaterUse" data based on different categories in a sankey chart. For each category, I have sets of Running_Sum table calculations that add/subtract the values of each row with the values of previous rows (I called them P6_Used to P4-Used in the folder Supply Calc as an example of three categories) . And I finally want to combine them all in a new indicator, called "FinalWaterUse" where I'm using the data for each category combined together, using case statement:
case [Supply Categoty]
when 'Graywater' then [P6_Used]
when 'Reclaimed Building' then [P5_Used]
when 'Rainwater' then [P4_Used]
else 0
END
There are two problems: first, I can not use them in case statements, since it can not mix aggregate and non-aggregate argument.
Second, if I want to use any LOD expression as a solution, it can not read table calculation, and says table calculations and ATTR are not allowed in aggregate expression! and unfortunately I'm not sure how to use SQL file here to solve this problem.
I really appreciate it if you could help me solve this issue.