• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot for understanding sales figures

S

Surobhi

New Member
Hi,

I have sales figures from different countries. The names of a product is different in different countries. Hence I need to use a mapping table to say that the Product A is called A1 in one country A2 in another and so on. I need to compare the sales of Products A,B,C and D.The data is huge ( thousands of rows) and I want to avoid using so many vlookup criteria. Hence I am using Power Pivot to do that. But somehow I am unable to set the right relationship. Please help. I have attached the working file for your reference.Thanks.
 

Attachments

  • Sales-Countries.xlsx
    341.2 KB · Views: 14
Hi Surobhi,

Check the attachment.

Regards,
AM:)
 

Attachments

  • Power Pivot for understanding sales figures new version.xlsx
    353.7 KB · Views: 9
ashish mehra said:
Hi Surobhi,

Check the attachment.

Regards,
AM:)
Click to expand...

Hello Ashish,
Thanks for your reply. Unfortunately, I am using Excel 2010 and cannot read your file created in 2013 I suppose properly. The data source doesn't get linked.

Can you write to me, what additionally you have done to solve the issue. And yes, what you did looks like the solution to my problem.

Thanks,
SD
 
Hi Surobhi,

I am using 2013 version. You can create the relationship as created in the picture & hope you will get the desired result.

In case the problem persist then you can write back & some one will help you out.

upload_2014-12-23_15-3-39.png
Can somebody share the reason of file not working in 2010 made in 2013.

Regards,
AM:)
 
Last edited:
Hi Surobhi,

See the file, I had created in Excel 2010, see I had included a calculated column in both the tables in power pivot and than use that column to create relationship between main table. See sheet4 to see the PT.

Regards,
 

Attachments

  • Sales-Countries.xlsx
    360 KB · Views: 10
Thanks Ashish, Narayan and Somendra.

I have tried both in 2013(borrowed) and 2010. In 2010 it is bit tricky as a Vlookup Column needs to be added. In 2013 it is easy.

Also Somendra, the same thing does not work if the column values are not unique. Excel does not allow to create a relationship in that case.
Ashish, thanks for the file. I am trying to do create a Chart with unique Year on X axis and countries as different series. Something of the sort , that is attached. If you have an idea please share.
 

Attachments

  • Power Pivot Yearly Sales.xlsx
    358.4 KB · Views: 3
Hi Surobhi,

Please check the attachment in which I have made few changes to your data to match your requirement. I have merged the countries data in a table.

Please check if it suits your requirement.

Regards,
AM:)
 

Attachments

  • Power Pivot for understanding sales figures new example.xlsx
    221.3 KB · Views: 7
Thanks Ashish.
But my the framework has changed.
What I want is when I select a Brand A in slicer, the chart should display the respective (a1,a2,etc) products related to A, country wise and I should be able to see the differences between the sales from different countries with year on the x-axis.
Fundamentally a Brand A , may have sub-brands a1,a2,a3 in Germany and Sub-brands aa1.aa2.aa3 in France.

Thanks again for posting,
Surobhi
 
You must log in or register to reply here.
Back
Top