Dear Team,
I have to implement analytics scenario which contains Cumulative Percentage (%) calculations for Items.
I have a dataset containing Business Unit, Items and Inventory Value of Items.
To Calculate Cumulative Percentage, first I have to sort items in descending order based on their Inventory Value. Then I have to calculate Individual items inventory value % as below:
Item1 Inventory % = ( Item1 Inventory Value / Inventory Value of All items ) * 100
Item2 Inventory % = ( Item2 Inventory Value / Inventory Value of All items ) * 100
Then Cumulative percentage for Item1 = Item1 Inventory %
Then Cumulative percentage for Item2 = Item1 Inventory % + Item2 Inventory %
and so on for remaining Items.
Business Unit and Items have one to Many Relationships. One Item can have multiple business units, If user selects One business unit, then cumulative sum should be for all the Items belonging to that Business Unit. If No Business Unit is selected then cumulative should be for all the items irrespective of Business Unit.
We tried to achieve this using below expression:
RangeSum(Above(TOTAL Sum(InventoryValue / vInventoryValue), 0, RowNo(TOTAL)))
where vInventoryValue is variable which contains Total Inventory Value of all items.
But This expression is taking lot of time to get calculated at front end. And also after applying filters (selecting Multiple Business Units) not giving desired results.
Request you to provide solution/workaround for same.
