Forum Discussion
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 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
11 Replies
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
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
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
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
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.
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
