competition sales data analysis

[hemantsingh]

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 quantities are collected weekly i.e once in one week. I need to analyse 8 weeks of data wherein i need to prepare a dashboard in which i need to show my company data along with other company's data. Moreover, need to show how much we sold a certain product & how much is sold by competition for some chosen week.

   I am able to create dashboard on each table independently. Somehow i am not able to create a dashboard wherin i can show mine & competition companies sales data together.

   Any guesses how to start it??

Regards,

Hemant

[v-jiascu-msft]

Hi @hemantsingh

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

[hemantsingh]

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]

Hi @hemantsingh,

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

Best Regards!

Dale

[hemantsingh]

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,

[v-jiascu-msft]

Hi @hemantsingh,

It's very similar with my demo. I have made some changes. Please verify it before using though I am sure it's right. One tips: don't use the column in the two table. Use the special table to bring them together.

1. Create a table with all the brands. 

Brands =
DISTINCT ( UNION ( VALUES ( Table1[Brand] ), VALUES ( Table2[Brand] ) ) )

2. Create a table with all the Material Categories.

Material Category =
DISTINCT (
    UNION (
        VALUES ( Table1[Material Category] ),
        VALUES ( Table2[Material Category] )
    )
)

3. Create a table with all the weeks.

Week-calendar year =
DISTINCT (
    UNION (
        VALUES ( Table1[Week-calendar year] ),
        VALUES ( Table2[Week-calendar year] )
    )
)

4. Create a measure for "Qty".

Quantity2 =
SUM ( 'Table1'[Qty] ) + SUM ( 'Table2'[Qty] )

5. Create visual.

Best Regards!

Dale

[v-jiascu-msft]

Hi @hemantsingh

Could you please mark the proper post as answer if it worked? More about this topic, please feel free to post here.

Best Regards!
Dale

[hemantsingh]

Hi @v-jiascu-msft,

   My apologies for the delay in response. Got stuck in a project. Further, I tried your last provided solution. everything seemed good but whenever i tried to choose a date from the slicer i loose the value of brand from my own table . Only competitors brands & quantities are visible. The moment i unceck the date filter visual gets my brand too in it.

   I am working to find out a way to resolve this.

Regards,

Hemant

[v-jiascu-msft]

Hi @hemantsingh

The field of date slicer should be from table "Week-calendar year". And the same with other slicers. The three new tables bring all together.

Best Regards!

Dale

[Back2Basics]

If you can put together a table with a list of unique products you can create a relationship between your two tables and the unique product list. Once those relationships are in place you should be able to create a clustered column chart to show the sales for both your company and competitors next to each other. I would suggest using the product types as the axis and seeing where you get from there??

Best of luck

[hemantsingh]

Hi @Back2Basics, ,

   I tried to do the same but somehow i am not able to see my brands sales qty on the same visual along with competitors sales qty. I want to show the week wise sales data comparision. I.e week no. on 1st slicer, Product Category on 2nd Slicer & brands on x axis in visual & sales qty on Y - Axis.

   I can certainly utilise the idea shared by @richbenmintz for creating a union between tables. Doing this on small tables with few thousands rows in each table is good but i am taking about tables that has more than 50 lakhs of rows each. Doing a union will be a temporary solution as over the period of time data will grow & PBI will strt showing lags.

Hope you understand my concern. Somehow i like the idea of @v-jiascu-msft, I am wondering if my scenario can be mapped by creating multiple calculated tables. I have shared the dummy data for your better undersatndings.

  Awaiting further ideas.

Regards,

Hemant

[richbenmintz]

you would need to either union your two data sets together in Power Query or create a product table then create a relationship between the new dimension and your facts. the facts "US" and "Them" woould then be sliceable by the shared dimension products.

Good luck