Hi all
I have been working to resolve an issue for the past 3 days without much success. Have tried various permutations and combinations to no avail. Appreciate if anyone can suggest ideas to solve this problem
here are the details:
A. Data Sources
1. Data Table comprising National Sales for all stores (National Data). This comprises Store IDs and Sales among other fields
2. Competitors Lookup Table - This comprises Stores operating under ACME. There are two columns as follows:
ACME ID = Store IDs for ACME Stores
Competitor IDs = For each Store there are around 10 local competitor stores (each with their unique IDs). However due to close proximity in some cases, ACME's own stores are also part of the competitor group. Also as I am calculating market share, the sales for a particular store are also included in the competition
So for Example: the competition for ACME1 looks something like this in the Competitors Lookup Table
ACME ID CompetitorID
ACME1 ACME1
ACME1 ACME3
ACME1 ACME7
ACME1 Other1
ACME1 Other2
AMCE1 Other3
ACME1 Other4
ACME1 Other5
ACME1 Other6
ACME1 Other7
3. ACME Stores Lookup Table: This comprises a list of ACME Stores with their IDs and other attributes e.g. postcodes etc. I have added a column here to mark each row as "ACME" which then allows me to lookup the sold items from the National Data list for ACME stores.
B. Linkages:
CompetitorID links to National Data IDs - This allow calculation of Local Competition Sales (this is a many to many relationship)
ACMEID links to National Data IDs - This allows calculation of ACME Sales (one to many)
Measures:
ExtCompetition = Calculate ([NationalItems] , Values (ExtCompetition[ACMEID]))
ACMEItems = calculate ([NationalItems] , filter(ACMEStores, ACMEStores[Group] = "ACME"))
I am plotting the sales of both the competition and ACME over time
Now the issue:
I can get the competitors sales correctly in that it includes everything that needs to be included
For those ACME stores that have internal competitors (competition from other ACME stores), their sales are added even when I only select one ACME store on the slicer. Those stores which do not have an internal competitor are showing correctly.
If I remove that internal ACME competitors from the competition list (data source) completely, the competition sales are reduced but I get correct sales for the individual ACME store selected on the slicer. This then means that while the ACME store level sales are now correct, the competitor sales are incorrect.
So essentially, including the ACME internal competition adds to both the competition as well as ACME store sale (even when one store is selected).
Really not able to understand how to solve this... greatly appreciate any help
@amitchandak
@parry2k
@mahoneypat
@az38
