I'm sure this is related to data modelling.
trying to do sum (values)
I have a Field A as Sales Person
Field B as Locations. Each Location is tagged with a number for Sales Person.
John has location No as 2 but has 2 different locations, one is NY & NJ.
So when I sum it up, it combines NY & NJ, Instead of seperating sum for NY as 150 & NJ as 376.
I know this may require a sample or actual data to work on...Due to sensitivity, I cannot post it here.
But I can e-mail a sample if someone wants to help?
Been stuck on this issue since a week.
Any help is appreciated. @sunny_talwar
Note: Expression I'm using us just Sum(Sales) Nothing fancy.
Tried, doesn't work
Values get combined, I'm thinking my data model is having issues.
To seperate values, I used ApplyMap from main Sales table, linking it to Fact table.
So when it gets mapped to Fact, values get messed up.
Reason: Fact table do not have those missing sales person- so thats not showing. And whatever sales people are there, they are combined together to show as single location.
Or Single location should have multiple sales people but it is showing only 1.
Is your data coming from a single table or multiple tables? I am guessing that your joins (explicit or implicit) might not be correct which is why you see the data like this. For example, if you have sales by sales person in one table, but this table either doesn't have location information or doesn't join to another table where you have sales person and location information, there will be an issue when you try to display Sum(Sales) by salesperson and location. Sales doesn't have any idea about location. All it knows is that it is associated with a certain salesperson. But this is just a thought. It is difficult to know without seeing what you have.
Sales Person & Location are coming from same table.
Note: Sales person & location is just an example wordings..
In total I've 4 tables & 1 Master Calndar.
1st table has Sales Person, Location & their Location No's
With first table I'm creating an apply map to map my 2nd table (Net), based on Location No's to bring in Sales Person & Location. Its from excel file.. sole purpose of this table is to link locations, sales people, CLM, Location No's to actual data coming from DB.
Here I'm taking Sum(Sales Or Net) with regards to apply map from 1st table.
For 3rd table I've account No's & another unique field as CLM. This CLM is also in 1st table. Based on that, I'm using apply map to bring in Sales Person, Location & Location No's.
These 2 fields will be used to link to 2nd table based on Sales date from 1st table & Account Date from 3rd table. Dropping these 3 fields from 3rd field to avoid cirsular ref.
With 4th table, its same again, apply map to 3 fields based but this time not on Location No's, only on Sales Person & Location Name.
Linking 4th table to 2nd table based on Sales, Location Name & Dates.
And then 5th is Calendar, getting dates from 3rd table, Accounts.
I know this may not give complete picture of what I'm doing until you see actual script or load it & see.
Please let me know if you need sample app?
I do not want to upload the app here, also wanted to do w/o scrambling the data. So I can explain better..