Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

competition sales data analysis

I got two tables A & B. Table A has competition companies A1,A2 & A3 sales quantity for 3 product categories. Table B (my comapny) has sales quantity of same 3 product categories. The sales quantitie...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi Anonymous

 

There are already many good ideas. If your two tables have the same structure, uniting them with “Append Queries” in Query Editor is a good idea. It’s easy to make visualizations with one table. If the two tables don’t have the same structure, please post the structures here (maybe with dummy data).

 

If you don’t want to unite them, you could try this.

1. Create several tables with these formulas.

 

Companies =
DISTINCT (
    UNION ( VALUES ( Competitors[Company] ), VALUES ( MyOwn[Company] ) )
)

 

Date =
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )
 
Product Categories =
DISTINCT (
    UNION (
        VALUES ( Competitors[Product Category] ),
        VALUES ( MyOwn[Product Category] )
    )
)

2. Create relationships.

3. Create one measure if the value is unique when Date, Company, Product Category are same. It should be unique.

  

Quantity =
IF (
    MIN ( 'Competitors'[Value] ) = BLANK (),
    MIN ( MyOwn[Value] ),
    MIN ( Competitors[Value] )
)

4. Create visualization.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi v-jiascu-msft,

     

       Accept my sincere greetings.

     

    Further, if i choose to do a union on two tables than i have to make their structure i.e no. of columns identical which i don't want to do on first place. I liek your idea of making multiple table. I tried to do so but somehow i am unable to resolev the issue. I have made a dummy data for you which is very close to my original data accept the no. of columns. For sake of dummy data i kept the no. of columns same in two dummy tables. Table one has my co. brand as apple & along with other attribute on which i would like to slice the data. Table 2 also have the identical data but in brand column  i have made changes. Now i want to create a visual where i want to make a brand wise sales comparision of 2 weeks i.e 22-2017 & 23-2017 across different sales office. I hope you  got the idea.

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous,

       

      One question: you said there are three companies in one table. I can't identify them. Could you point out it?

       

      Best Regards!

      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        hi v-jiascu-msft,

         

           Brand column can be taken as companies column. one table has only apple as a brand consider that company/brand as mine. Other table has 3 different brands/companies selling the same product over the same period of time i.e week-calendaryear.

           Further the competition data is being recorded by the sales executives standing on different outlets mentipned as outlet codes which are moreover a multi brand sellers/retailer.

         

        Regards,