NEAK task 2.
Your task is to analyse the company turnover data of pharmaceutical products subsidized by the National Health Insurance Fund of Hungary (Nemzeti Egészségbiztosítási Alapkezel, NEAK). The imported data warehouse consists of four tables that are to be linked by appropriate identifiers to make it possible to generate multiple Power Pivot reports and charts.
Define 3-4 key performance indicators, select 3-4 companies based on their performance (e.g. market share, total turnover, smallest companies) and conduct the analysis. When creating pivot table reports, use KPIs, as well as simple and complex DAX functions. Use Power View to dynamically visualize your data. Use Excel Power Pivot Add-in for analysis, as the data is in the Business [login to view URL] file repository.
1. Learn about the data warehouse in Business [login to view URL] file that contains the first half-year 2013 data of pharmaceutical companies and the tables Dates, Regions and Monthly_Turnover. Study the contents of the four tables and the record description.
2. In Monthly_Turnover table, create a field that is suitable for identification. Create properly the relationships between the tables.
3. Calculate the total turnover of the 3-4 selected companies and the turnover of the first and second quarter of 2013 by a DAX function.
4. Prepare a KPI for the second-quarter turnover of the chosen 3-4 companies, target value is the first quarterly turnover.
5. Make a Power Pivot report on a chosen company's quarterly turnover and KPIs together with the names of counties. How did the turnover evolve based on the KPI signal?
6. Create a Power View diagram, which represents the monthly turnover of a company's pharmaceutical turnover. Based on the diagram, what conclusions can you draw about the company's turnover in the given period?
Make a presentation on the completed analysis and upload it along with the calculations. The formal requirements of the presentation are the following: 10-15 slides, presentation of your findings and conclusions with illustration, design, animation, transition, including the date of creation, page numbers.
Source: Business intelligence.xlsx. You can find it in the attached files.
You can find information about the extension here:
Microsoft (2018) Start the Power Pivot add-in for Excel. Available from: [login to view URL] [Accessed: 5 January 2018]
Microsoft (2018) PowerPivot for Excel Tutorial Introduction. Available from: [login to view URL](v=sql.110).aspx [Accessed: 5 January 2018]
Microsoft (2018) Turn on Power View in Excel 2016 for Windows. Available from: [login to view URL] [Accessed: 5 January 2018]
Microsoft (2018) Power Pivot: Powerful data analysis and data modeling in Excel. Available from: [login to view URL] [Accessed: 5 January 2018]