Aggr for 2x dimensions - sorting by expresion problem (Pareto analysis)
Hi, I want to calculate running total % of sales for pareto analysis. Everything works fine when working with one dimension. Lets say i have dimension "Key" and wanna to sort it by "sum(Sales)" in descending order using Aggr. So i do:
The problem with the above is that Aggr sorts rows within Aggr by Key, but GLOBALLY not by each month SEPARETLY. Which leads to incorrect results. Is there any way to sort data within Aggr by Key & Month?
I came up with an ugly solution, thats is not flexible in any means (requires loading script changes). I just loaded every possible combination of Key & Month into my model and created new dimension KeyMonthCombined. Then expression looks like: