Sum from one numbered list module to another numbered list module

CommunityMember120559
CommunityMember120559
in Modeling

I have imported my transactional data as a flat table using a numbered list. Something like this:

ca1.JPG

 

I want the user to go into a second module where they can select drop-downs for City, and it will sum the sales. This second mapping module must be a flat table too (numbered list) because if I were to dimensionalize it, the model would explode (my real data has many more attributes than just City).


ca2.JPG

 

How can I SUM the Sales into the second module? I get an error when using SUM, because there are no list dimensions in the 2nd table except for the numbered list of Mapping.

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @CommunityMember120559 ,

     

    You will have to make a line item in the transactional module a list formatted line item, whether that is what you currently have as City or a new one (preferably, your current line item of City should be formatted as a list).  In the 2nd module, you will need to have it dimensionalized by the City list so the sum will work.

     

    You stated this will **** up your model because you have more attributes.  What are all of the attributes?  Do some of them relate to each other like a hierarchy?  For example, if you have city, state, and country, you could create a hierarchy for of just that (city rolling up to state rolling up to country), but the module to pull the Sales data in would just come from city so state and country would automatically aggregate.

     

    Hope this helps,

    Rob

  • CommunityMember120559
    CommunityMember120559

    I have 15 unrelated attributes of the transactional data, all of which can be used in the Mapping table (but not all are required). For example for Mapping 1, the user might need to select only City, whereas on Mapping 2, they might select five of the 15 attributes to map the sum of Sales. Each Mapping has different attributes of the 15. If an attribute is not used for a given Mapping, then the formula should sum all values for that attribute.

    The existing tool in Excel achieves this using Data Validation on the attributes in the Mapping table. If the user enters *, SUMIFs works correctly to pull in all values for that attribute.

    The transactional table is already list formatted, but if I have to create a dimensionalized mapping table it will be impossibly huge, and defeat the purpose of being able to map in the first place (it becomes not a mapping table, but a summing table).

Categories