Hi All,
Wondering if anyone else has run across this problem, and hoping someone might have some advice.
At the moment we sum all invoice lines against account code to create a measure of total sales. We have sales people (one to each account) and this works fine, but we also have BDM’s on some accounts, and in some cases an account can have multiple BDM’s (e.g. one in NSW, one in WA). When I add the BDM table in as a new dimension and tie it to the account code, it causes the revenue measure to multiply by the number of BDM’s per account. Any ideas on how to avoid this, and just have the BDM as a dimension that could be selected by, but not measured by any more than account code is now ?
Let me know if I can clarify, but essentialy, I have many lines adding up to one account code revenue value, then many BDM’s to one account code , which is causing revenue measure to multiply.
Thanks,
Ian
Hi Ian, it does sound as though the issue is a result of the many to many relationship you have with the BDM’s and the accounts. I’m not sure of how your database is currently designed, so these comments are without any knowledge of the current groupings.
Sub groups of dimensions, which I’m assuming BDM was introduced as a grouping of the main ‘Customer’ dimension, should only be linked by a single value at the main dimension (Customer) level. If the dimension was added in it’s own right, ie not as a grouping of another dimension, then it should link back to the transnational data. In this instance you should then have a reference to the BDM at the time of the sale for example, and then each record would only have a single BDM associated, which could link back to a single BDM dimension. This should resolve the many to many relationship and the results you’ve encountered.
If the above isn’t helpful or I’ve misunderstood, please feel free to elaborate a bit more on any of the logic that may dictate why an account may have more than 1 BMD, eg is it decided by location?
I hope this helps but feel free to let me know if you need any more advice.
Regards,
Lee
Hi lee,
Thanks for the reply.
Each of our BDM’s is assigned to a state, and then to an account. In most instances, there is only one, but in some instances we have a national account, which falls across multiple states and attracts more than one BDM (e.g. SUPER BASS FISHING could have a BDM in QLD , NSW, and SA).
In our current non-phocas tracking all sales for the account count against each BDM in each state (aggregate), so I can’t assign a BDM per line. Our inside sales guys are setup this way and works fine (one sales order to one sales person).
Ideally I want to be able to have the BDM’s as a dimension, even if the total for them is larger than the overall sum of values, though showing them as a dimension without a total until you change back to an account code view would be ok too.
I could build a table behind the scenes with each sales order duplicating by BDM, as you suggest, then add it to a separate database, but I was hoping to find a way to have Phocas do this. The table would be pretty large with the duplication, though not impossibly large.
Ian