Sum one Line Item based on the value of another Line Item

Options
gordon_sneddon
gordon_sneddon Member, ALL USERS, GroupMember, Community Member, Community Pioneer Posts: 4 Master Anaplanner of the Year
in Planning & Modeling
Hi,
 
I'm wondering is someone could help us with the following:
 
We have a transaction module called "Data Store" which is made up of line items (columns) and a Transaction Unique Key (List - Rows)

Refer "Image 1" for the Pivot View...
 
Refer "Image 2" for the Model view. (This is a subset of the data contained in the module, first column being the unique ID)
 
There are 84 line items in the model, for each Unique Key there and there are 20 line items of which only one line item per Unique Key is populated (currently with a value, which is duplicated from the "Amount" line item), leaving 19 line items not populated resulting in a large amount of sparsity.
 
In order to reduce the size of the module we would like to reduce the 20 line items down to one line item, with the new line item containing the name of old line item (or something like that), let's call the new line item "Category".
 
In another module, which already exists, we would like to be able to sum up the amount from the Data Store module based on time period, branch name, division and category (there is one more line item which I have left out of the examples as it will work the same way as the branch name and division etc and not required, however handy to know it exists). Something else to note is the formula is to be written in a line item which is not list formatted, it's just a line item, which cannot easily be changed.
 
Normally we'd use a formula such as:
 
Data Store.Amount[SUM: Data Store.Time Period, SUM: Data Store.Branch Name, SUM: Data Store.Division, SUM: Data Store.Category]
 
However this will not work as "SUM: Data Store.Category" cannot go down that one step further (to something like: "SUM: Data Store.Category.CAT1" for the contents of the cells. Even using a SELECT won;t go down to that level, otherwise that would be ideal.
 
We could create an intermediate module with a list formatted Dimension for the Category, however there are 5 dimensions, then create the formula to read from that, however even when sub setting the dimension lists the cell count of the intermediate module is larger than the cell count of the Data Store.
 
Has anyone come across this sort of issue before and how did you manage to resolve it ?
 
Tagged:

Comments

  • gordon_sneddon
    gordon_sneddon Member, ALL USERS, GroupMember, Community Member, Community Pioneer Posts: 4 Master Anaplanner of the Year

    Hi Anirudh,

    Thanks for your replay..

    The current set up is more like the following.

    Current.png

    Where the data store has  'Branch', 'Division', 'Time Period', 'Description' and 'Amount' line items, it then also has number of other line items which will only be populated with a value (from the Amount line item) when the description is meets a criteria (i.e. If the Description is Product1 then Line item Cat1 is populated with the value from the Amount line item and all other Catx line items contain zero, when the description is Product2 then line item Cat2 is populated with the value from the amount line item and all other Catx line items contain zero.

    The module that is looking up the Data Store looks like:

    Report_Current_Result.png

    And the Blueprint for this is:

    Report_Current_Blueprint.png

    Which uses a formula of:

    'Data Store - Current'.'Cat1'[SUM: 'Data Store - Current'.Branch, SUM: 'Data Store - Current'.Division, SUM: 'Data Store - Current'.Time Period]

    With only the 'Catx' number before the opening square bracket changing for the line item formula's.

     

    I'd like to reduce the size of the data store module by turning changing it to something like:

     

    Preferred.png

    Where I have 1 Category Line Item, containing the Category and I get the value from "Amount" Line Item.

    However when I use a similar formula to the one above, I get:

    Report_Improved_Result.png

     

    As I'm not able to get the formula to distinguish between the individual categories (The formula is only in the Cat1 line item (that's why the others show zero) the amount is summed up no matter what the Category is ? (i.e. you see in the current report that the total for all 4 categories added together in May is 550, however in the improved report each line item with the formula would contain 550.

     

    I can use an intermediate module, which would turn Category into a Dimension however this would use more cells that the extra line items in the Data Store.

    And where the result is to end up is in a current module and is a line item.

     

    Hope that make a little more sense and once again, thanks for your assistance.

     

  • gordon_sneddon
    gordon_sneddon Member, ALL USERS, GroupMember, Community Member, Community Pioneer Posts: 4 Master Anaplanner of the Year

    Hi Anirudh and David,

    Thank you both for your reply's.

    Anirudh - I was implementing your suggestions from your reply when I realised I have category as list in my actual modules, however did not have it as a list in the samples I placed here (as also pointed out by David). However using Anirudh's logic and a slight modification I've been able to make it work. Obviously I couldn't post the actual modules due to the data they contain.

    David - I'm offsite today, however will look into your suggestions also and provide feedback and accept as solutions over the next two days.

    Appreciate both your help. And will get back to you...

  • gordon_sneddon
    gordon_sneddon Member, ALL USERS, GroupMember, Community Member, Community Pioneer Posts: 4 Master Anaplanner of the Year

    David,

    Anirudh's answer allowed me to solve my immediate issue and reduced the modules cell count by slightly over 35%. I saying that though, I have slotted time in the near future to look further into your recommendations and reviewing your blog.

    Thank you both for your help.

Categories