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,

 

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's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi Anonymous

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous

       

      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