Sum from one numbered list module to another numbered list module

Options
CommunityMember120559
CommunityMember120559 Member, ALL USERS Posts: 2 New Contributor
in Planning & 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:

Comments

  • CommunityMember120559
    CommunityMember120559 Member, ALL USERS Posts: 2 New Contributor

    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