Combine multiple values in a dimension in Chart Box
I have the below table. I'm trying to create a pivot table (second table below) such that the locations "TR02,TR03,TR04" are pooled/aggregated in a single cell and renamed as "incomplete". Similarly location "SD" pooled and renamed as "finished". Then i can create a bar chart using this table as a filter so that it shows sum of completed qty for both "incomplete" and "finished" categories.
I used MATCH,AGGR,SET ANALYSIS and IF statements in calculated dimensions/Expressions tab for chart properties but they don't work. Please help me as it's driving me crazy.