RPBIData Analysis & Reporting using Power BI
Rating:
English
Advanced
FACE 2 FACE
ON SITE TRAINING
LIVE VIRTUAL
TRAINING
COACHING
& MENTORING
SELF-PACED
TRAINING
Select Date
Download Brochure
Course Overview
Almost every organization, whatever its size, is drowning in data. Businesses need to analyze accounting data, marketing data, service data, budget data, human resources data, manufacturing data, and organizational data, among others.
Organizations need to:
→ Analyze historical performance data vs budgets;
→ Measure, track and predict sales
→ Finding issues before they escalate
→ Develop their customer relationship
→ Optimize processes and operational performance
Finance, marketing, HR and production teams in most organizations spend hours and hours of time crunching data in Excel. There is never enough time to fully analyze the data.
By using PowerPivot and Power BI you can analyze data in ways that go way beyond “regular Excel”. You can get more done in less time. PowerPivot is arguably said to be the best new feature to hit Excel in 20 years. This went extra miles with the introduction of Power BI in Sept 2015. PowerPivot is a free ad-in only available in Excel 2010 any edition) or Excel 2013/16 (professional edition). It enables users to manipulate huge data sets coming from different sources in the most efficient way. Power BI is a dashboarding tool that generates the most dynamic and amazing reports at an end user level. It has taken the word by storm.
The need to have IT support to import, sort and manipulate data can be part of your distant memories after the full understanding of PowerPivot and the various applications. It blends together all the power of Business Intelligence (BI) along with the user-friendly environment of Excel. You will master tons of BI tools within 5 days. The value add you will bring back to your organizations after attending this program is immense. Microsoft’s plan is to “bring Business Intelligence to the masses” and PowerPivot, then Power BI, is their first step in the plan.
Millions and millions of rows can be imported directly and quickly from your databases straight into PowerPivot, bypassing Excel, giving users in various organizations tools that could not be done before in standard Excel. In PowerPivot you can read data from different ranges to amalgam together in one pivot table. For example imagine you have separate data bases related to different departments, regions, products, salesmen, and customers to name a few. Then your manager would like to know the sales made in this particular region for this product by this salesman to this customer year to date vs the performance of the same period in the previous year. Then your manager would like to have a dashboard where he can get all of these questions answered with few clicks. A normal pivot table will never answer this question and many more. Let alone the inability of pivot tables to handle significant data size. A normal pivot table can start choking with even 50k rows of data. One PowerPivot can easily handle data comprising of 200, 300 or 400 million rows. It is massive!! PowerPivot is a real revolution inside the world of data analysis because it gives you all the power you need for to perform complex analysis of data without requiring the intervention of BI technicians.
PowerPivot and Power BI came with their powerful DAX formulas (Data Analysis Expressions). You will be surprised of what these expressions can do to your data compared to those that are standard in Excel. You will learn the basics of DAX expressions then we are going to move up the ladder swiftly until you are able to create KPI dashboards (Key Performance Indicators) that can be shared with your colleagues. When you go through the outline, you will realize how considerable the details and practical examples we will cover. You can produce Business Intelligence reports yourself. Power BI is one of the newest products of Microsoft that is freely available to all users. It is entirely built on the full understanding of PowerPivot and DAX. Only then one can see the full potential of this amazing tool that allows users to build the most fascinating dashboards that can be shared in iPads, cell phones and through social media creating clusters of who can view the data and view what. It gives users the ability to “discuss” results within every group. It is the best tool available to end users to build the most comprehensive and the most dynamic dashboards ever. Very very impressive!
BI Skills are not required for this course. You just need to have a workable knowledge in Excel to master all contents of this very intensive course. You need to bring your Laptop with Excel 2010 (any edition) or Excel 2013/16 (professional plus). Excel 2016 professional plus is preferred for many reasons. Excel 2007 is not allowed at all. Every delegate will receive a book related to PowerPivot.
Organizations need to:
→ Analyze historical performance data vs budgets;
→ Measure, track and predict sales
→ Finding issues before they escalate
→ Develop their customer relationship
→ Optimize processes and operational performance
Finance, marketing, HR and production teams in most organizations spend hours and hours of time crunching data in Excel. There is never enough time to fully analyze the data.
By using PowerPivot and Power BI you can analyze data in ways that go way beyond “regular Excel”. You can get more done in less time. PowerPivot is arguably said to be the best new feature to hit Excel in 20 years. This went extra miles with the introduction of Power BI in Sept 2015. PowerPivot is a free ad-in only available in Excel 2010 any edition) or Excel 2013/16 (professional edition). It enables users to manipulate huge data sets coming from different sources in the most efficient way. Power BI is a dashboarding tool that generates the most dynamic and amazing reports at an end user level. It has taken the word by storm.
The need to have IT support to import, sort and manipulate data can be part of your distant memories after the full understanding of PowerPivot and the various applications. It blends together all the power of Business Intelligence (BI) along with the user-friendly environment of Excel. You will master tons of BI tools within 5 days. The value add you will bring back to your organizations after attending this program is immense. Microsoft’s plan is to “bring Business Intelligence to the masses” and PowerPivot, then Power BI, is their first step in the plan.
Millions and millions of rows can be imported directly and quickly from your databases straight into PowerPivot, bypassing Excel, giving users in various organizations tools that could not be done before in standard Excel. In PowerPivot you can read data from different ranges to amalgam together in one pivot table. For example imagine you have separate data bases related to different departments, regions, products, salesmen, and customers to name a few. Then your manager would like to know the sales made in this particular region for this product by this salesman to this customer year to date vs the performance of the same period in the previous year. Then your manager would like to have a dashboard where he can get all of these questions answered with few clicks. A normal pivot table will never answer this question and many more. Let alone the inability of pivot tables to handle significant data size. A normal pivot table can start choking with even 50k rows of data. One PowerPivot can easily handle data comprising of 200, 300 or 400 million rows. It is massive!! PowerPivot is a real revolution inside the world of data analysis because it gives you all the power you need for to perform complex analysis of data without requiring the intervention of BI technicians.
PowerPivot and Power BI came with their powerful DAX formulas (Data Analysis Expressions). You will be surprised of what these expressions can do to your data compared to those that are standard in Excel. You will learn the basics of DAX expressions then we are going to move up the ladder swiftly until you are able to create KPI dashboards (Key Performance Indicators) that can be shared with your colleagues. When you go through the outline, you will realize how considerable the details and practical examples we will cover. You can produce Business Intelligence reports yourself. Power BI is one of the newest products of Microsoft that is freely available to all users. It is entirely built on the full understanding of PowerPivot and DAX. Only then one can see the full potential of this amazing tool that allows users to build the most fascinating dashboards that can be shared in iPads, cell phones and through social media creating clusters of who can view the data and view what. It gives users the ability to “discuss” results within every group. It is the best tool available to end users to build the most comprehensive and the most dynamic dashboards ever. Very very impressive!
BI Skills are not required for this course. You just need to have a workable knowledge in Excel to master all contents of this very intensive course. You need to bring your Laptop with Excel 2010 (any edition) or Excel 2013/16 (professional plus). Excel 2016 professional plus is preferred for many reasons. Excel 2007 is not allowed at all. Every delegate will receive a book related to PowerPivot.
Key Takeaways
1
Work with huge separate databases
2
Combine data from different sources.
3
Use DAX to create calculated fields for slicing and dicing data.
4
Create visually appealing analytical models.
5
Publish dahshboards through Power BI.
6
You will finally start to get value from the huge data available to your organization.
National Association of State Boards of Accountancy
LEORON Professional Development Institute DMCC is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.NASBARegistry.org
Course Outline
Day 1
PART 1: Working with classic Pivot Tables
→ Converting data to Tables and Pivot Tables
→ Top 10 functionality
→ Copying and pasting pivot tables
→ Manipulating data to show % of column, row and grand total
→ Understanding a pivot table you never created
→ Pivot Chart
→ Building a dashboard in Excel from scratch
Case: Practical application
→ Top 10 functionality
→ Copying and pasting pivot tables
→ Manipulating data to show % of column, row and grand total
→ Understanding a pivot table you never created
→ Pivot Chart
→ Building a dashboard in Excel from scratch
Case: Practical application
PART 2: First practical example of Power Pivot (only Excel 2013 Professional Plus)
→ Introduction to Power Pivot
→ When to use Power Pivot
→ Importing data into Power Pivot directly from Excel
→ Importing data into Power Pivot from data warehouses
→ Importing data from Access, SQL, Oracle or SAP
→ The data view vs the diagram view
→ Building relationships between tables
→ Relational Database
→ One-to-many relationships
→ Editing relationships
→ Deleting relationships
→ Building the first Pivot Table from Power Pivot
→ The power of filtering context
→ Retrieving data from different ranges and tables
→ Introducing Slicers
→ Formatting slicers
→ Using slicers as control selection in building dashboards
→ Multiple selections from slicers
→ Producing the first report from Power Pivot
Case: practical application
Day 2
PART 3: Starting with the infrastructure to build a dash board using Power Pivot from scratch
→ Import the data from Access
→ Specifying the data to import using the data import wizard
→ The Preview and filter option
→ The Select Related Tables option
→ Only select the tables related to a certain table
→ Filter out certain columns
→ Hide from Client Tools
→ Data types
→ Foreign Key vs Lock-Up Key
→ How to create a relationship between two tables if there is no apparent key
→ The Create Relationship Wizard
→ How to detect relationship automatically
→ DAX (Data Analysis Expressions)
→ Calculated Columns vs Calculated Fields
Case: practical application
→ Specifying the data to import using the data import wizard
→ The Preview and filter option
→ The Select Related Tables option
→ Only select the tables related to a certain table
→ Filter out certain columns
→ Hide from Client Tools
→ Data types
→ Foreign Key vs Lock-Up Key
→ How to create a relationship between two tables if there is no apparent key
→ The Create Relationship Wizard
→ How to detect relationship automatically
→ DAX (Data Analysis Expressions)
→ Calculated Columns vs Calculated Fields
Case: practical application
PART 4: Building the Dashboard in PowerPivot from scratch step by step
The purpose of this model is to allow the user to build a dynamic dashboard from scratch comparing data across different products, regions, salesmen, or any other criteria for both actual and forecasted. The comparison will include YoY% (year on year % growth) of the last month vs same month last year, last quarter vs same quarter last year, year to date vs same period last year for both actual and budgeted data. In addition, the dashboard will be automatically reporting on the best 10 products with all their corresponding figures of growth YoY. The dashboard will include sparklines, pivotcharts and all other supporting tables that are dynamically changing with the selection of control options. In order to be able to create such a dynamic dashboard, extensive discussion on the following DAX functions will be examined:
→ Related
→ Day, Mont, Year, Date
→ Lastdate
→ Dateadd
→ Calculate
→ All
→ Left, Right
→ Values
→ Hasonevalue
→ Format, Text
→ Divide
→ Isblank
→ Not
→ Totalytd
→ Totalqtd
→ Totalmtd
→ Related
→ Day, Mont, Year, Date
→ Lastdate
→ Dateadd
→ Calculate
→ All
→ Left, Right
→ Values
→ Hasonevalue
→ Format, Text
→ Divide
→ Isblank
→ Not
→ Totalytd
→ Totalqtd
→ Totalmtd
Day 3
PART 5: Very important notes in building dashboards using DAX
→ Beware of the “Naked” Columns when writing measures
→ Difference between referencing a column and a measure
→ Assign the measures to the relevant table
→ CALCULATE(), The basis of (almost) all the work in PowerPivot
→ Syntax
→ Example
→ Another one
→ Yet another Example
→ Additional Alternatives
→ The “ALL” Function (a.k.a. The “filterremover”)
→ The ALL() Benefits
→ 1.Percentage of Parent
→ 2.Negating the Slicers
→ Important
→ ALLEXCEPT()
→ ALLSELECTED()
→ Working with multiple tables
→ Lookup Tables and Data Tables
→ Viewing the relationships in Diagram view
→ CALCULATE() goes through relationships also
→ INTERMISSION
→ Disconnected Tables
→ Add the Parameters
→ Add a Harvester Measure
→ Have you consulted Field List?
→ Parameters are friends of Rows and Columns
→ Variations in Disconnected Tables
→ Filter()
→ Working rules
→ Thresholds with disconnected tables
→ Validation
→ Threshold with min and max boundaries
→ Difference between referencing a column and a measure
→ Assign the measures to the relevant table
→ CALCULATE(), The basis of (almost) all the work in PowerPivot
→ Syntax
→ Example
→ Another one
→ Yet another Example
→ Additional Alternatives
→ The “ALL” Function (a.k.a. The “filterremover”)
→ The ALL() Benefits
→ 1.Percentage of Parent
→ 2.Negating the Slicers
→ Important
→ ALLEXCEPT()
→ ALLSELECTED()
→ Working with multiple tables
→ Lookup Tables and Data Tables
→ Viewing the relationships in Diagram view
→ CALCULATE()
→ INTERMISSION
→ Disconnected Tables
→ Add the Parameters
→ Add a Harvester Measure
→ Have you consulted Field List?
→ Parameters are friends of Rows and Columns
→ Variations in Disconnected Tables
→ Filter()
→ Working rules
→ Thresholds with disconnected tables
→ Validation
→ Threshold with min and max boundaries
Day 4
PART 6: Creating Calendar Tables
→ Time Intelligence
→ Calendars
→ How to create a calendar table
→ What you need in a calendar
→ Getting started
→ Special Features of Calendar Table
→ DATESYTD()
→ Can the year-end date be changed?
→ DATESMTD() and DATESQTD()
→ TOTALYTD()
→ FIRSTDATE() and LASTDATE()
→ ENDOFMONTH(), STARTOFYEAR(), etc.
→ CLOSINGBALANCEMONTH(), CLOSINGBALANCEYEAR(), etc.
→ DATEADD()
→ Growth Since Prior Year (YOY)
→ Some Drawbacks of DATEADD()
→ SAMEPERIODLASTYEAR()
→ PARRALLELPERIOD()
→ NEXTMONTH(), PREVIOUSYEAR(), etc.
→ DATESBETWEEN()
→ Life To Date()
Case: practical example
→ Calendars
→ How to create a calendar table
→ What you need in a calendar
→ Getting started
→ Special Features of Calendar Table
→ DATESYTD()
→ Can the year-end date be changed?
→ DATESMTD() and DATESQTD()
→ TOTALYTD()
→ FIRSTDATE() and LASTDATE()
→ ENDOFMONTH(), STARTOFYEAR(), etc.
→ CLOSINGBALANCEMONTH(), CLOSINGBALANCEYEAR(), etc.
→ DATEADD()
→ Growth Since Prior Year (YOY)
→ Some Drawbacks of DATEADD()
→ SAMEPERIODLASTYEAR()
→ PARRALLELPERIOD()
→ NEXTMONTH(), PREVIOUSYEAR(), etc.
→ DATESBETWEEN()
→ Life To Date()
Case: practical example
PART 7: Conditions; IF(), SWITCH(), BLANK(), etc.
→ Using powerful DAX functions that will facilitate lots of dynamic modelling
→ IF()
→ BLANK()
→ DIVIDE()
→ ISBLANK()
→ HASONEVALUE()
→ More IF()
→ VALUES()
→ SWITCH()
→ SWITCH TRUE()
→ The “X” Functions (also called “Iterator” Functions)
→ SUMX()
→ MINX(), MAXX() and AVERAGEX()
→ FILTER()
→ COUNTX() and COUNTAX()
→ “X” Functions work with the “absentee” also
→ RANKX()
→ TOPN()
→ Multiple Data Tables
→ Merging two data tables into one Pivot
→ “Hybrid” Measures
→ Few Points to remember
→ Data Tables with different “Granularity”
→ Budget vs. Actuals Case: putting it together
→ IF()
→ BLANK()
→ DIVIDE()
→ ISBLANK()
→ HASONEVALUE()
→ More IF()
→ VALUES()
→ SWITCH()
→ SWITCH TRUE()
→ The “X” Functions (also called “Iterator” Functions)
→ SUMX()
→ MINX(), MAXX() and AVERAGEX()
→ FILTER()
→ COUNTX() and COUNTAX()
→ “X” Functions work with the “absentee” also
→ RANKX()
→ TOPN()
→ Multiple Data Tables
→ Merging two data tables into one Pivot
→ “Hybrid” Measures
→ Few Points to remember
→ Data Tables with different “Granularity”
→ Budget vs. Actuals Case: putting it together
Day 5
PART 8: Power BI Desktop
→ How to Install
→ Getting started
→ Creating the most amazing and dynamic dashboards
→ Very valuable visualizations
→ Changing the controls of your different visualizations
→ Controlling the whole dashboard or page by page
→ Downloading new visualizations from Microsoft
→ What if analysis
→ Different dashboards for different users
→ Cross referencing
→ Drill in and drill out
→ Hierarchy
→ Map showing your biggest sales region
→ Going through the performance of your products one by one
→ A pyramid of all your costs
→ Your users can get charts and data using Q&A just like Google
→ Get data from literally any source
→ Work on the tables, just like PowerPivot
→ Manage Relationships
→ New Measure
→ Calculated Columns can also be created in the same way, by right-clicking in the Field list
→ Creating Reports
→ Import already prepared PowerPivot Models
→ Publishing your dashboards to cellphones, tablets and desktops
→ Dashboard, amazing dynamic examples
→ Sharing the dashboard in various ways
Case: practical case in power BI
→ Getting started
→ Creating the most amazing and dynamic dashboards
→ Very valuable visualizations
→ Changing the controls of your different visualizations
→ Controlling the whole dashboard or page by page
→ Downloading new visualizations from Microsoft
→ What if analysis
→ Different dashboards for different users
→ Cross referencing
→ Drill in and drill out
→ Hierarchy
→ Map showing your biggest sales region
→ Going through the performance of your products one by one
→ A pyramid of all your costs
→ Your users can get charts and data using Q&A just like Google
→ Get data from literally any source
→ Work on the tables, just like PowerPivot
→ Manage Relationships
→ New Measure
→ Calculated Columns can also be created in the same way, by right-clicking in the Field list
→ Creating Reports
→ Import already prepared PowerPivot Models
→ Publishing your dashboards to cellphones, tablets and desktops
→ Dashboard, amazing dynamic examples
→ Sharing the dashboard in various ways
Case: practical case in power BI
Who Should Attend?
This highly practical and interactive course has been specifically designed for
→ Financial Analysts
→ Chief Financial Officers
→ Budgeting and planning specialists
→ Accountants
→ Financial government officials
→ Marketing officers
→ Insurance specialists and actuarists
→ Credit analysts
→ Treasury
→ Chief Executive Officers
→ Internal auditors
→ Venture capitalists
→ Corporate finance analysts
→ Risk managers
→ Board members
→ Investment bankers
→ Regulators
→ Private equity managers
→ Strategic planners
→ Trustees
→ Compliance officers
→ Management consultants
→ Bank lending officers
→ Compliance officers
→ Management consultants
→ Corporate Finance lawyers
FAQ
What language will the course be taught in and what level of English do I need to take part in a LEORON training program?
Most LEORON courses are delivered in English. However, there are some courses offered in Arabic, mainly online. For our in-house courses, sessions can be curated and delivered in any language upon request. In general, the best way to confirm language availability is to check with our Enrollment Managers for the most up-to-date information. Simply click on “Let’s talk on WhatsApp” to chat with us directly.
What formats are the courses offered in?
LEORON delivers training in various formats including face-to-face, live virtual sessions, self-paced learning, in-house delivery as well as online courses.
Are LEORON Public courses certified by an official body/organization?
Yes, most LEORON public courses are accredited by internationally recognized bodies such as CIPD, ATD, PMI, EdEx, and many others—depending on the course.
Who accredits LEORON’s training programs?
LEORON partners with over 20 international bodies such as PMI, CIPD, ATD, EdEx, NASBA, CISI, GARP, HRCI, SHRM, ACCA, ASQ, IIA, ILM, IAC, and others
Are CPD points or PDUs provided?
Yes, learners can earn CPD credits and professional development units (PDUs) including NASBA CPEs, PMI PDUs, CISI, GARP, HRCI, SHRM, and more.
How can I register for a course?
You can register through our website by filling in the inquiry form, or by speaking directly with one of our consultants via WhatsApp or email. Once we confirm your interest, we’ll guide you through the steps.
When is the registration deadline for public courses?
Registration typically closes 14 days before the course start date, with occasional late registrations accepted upon confirmation
What is included in the course fee?
The fee generally covers 5-star venue facilities, training materials, certified instruction, lunches and refreshments, plus certification and membership where applicabl0065
Are there group rates or discounts?
Yes, group bookings and corporate-level discounts are available. Learners are encouraged to reach out to discuss specific arrangements
What support is available with registration?
Enrollment Managers and a Registration Desk assist with the entire process, including deadlines, travel logistics, and course customization. As well as any other special requests you might have. Simply to go your preferred course and click on “Let’s chat on WhatsApp” to do so.
Can I request a bespoke course at my location or within my organization?
Yes, in-house training is fully customizable in terms of curriculum, language, delivery, and timing. You can suggest dates and locations. Simply to go your preferred course and click on “Let’s chat on WhatsApp” in order to address any questions or concerns in this regards.
What is the refund or cancellation policy?
Refund and cancellation policies vary depending on the course type and location. Generally, cancellations made at least 14 days before the course start date may be eligible for a full or partial refund, while cancellations made closer to the course date may incur a fee. For exact terms, please consult your Enrollment Manager or refer to the course confirmation email.
Can I register multiple employees from my company?
Yes. We support group registrations and offer corporate packages for organizations enrolling multiple participants. Our team can help coordinate the logistics for group bookings.
Who should attend these courses?
LEORON caters to a variety of professionals: from those seeking leadership development to project managers, HR specialists, finance professionals, cybersecurity, procurement, Ai enthusiasts and many others.
Do I need prior experience or academic qualifications?
Not always. Many specialized paths, like cybersecurity, accept learners without prior experience. However, some courses (e.g., PMI PDU-based ones) may have recommended prerequisites. Its always better to chat with one of our Enrollment Managers to discuss more. Simply to go your preferred course and click on “Let’s chat on WhatsApp” to do so.
Will I receive a certificate after completing the course?
Yes. Upon full attendance and successful completion, you will receive a certificate of participation or accreditation, depending on the course.
Are meals and refreshments included in face-to-face courses?
Yes. For in-person courses, lunch and coffee breaks are provided daily at the venue.
Can LEORON deliver a course in-house at our organization?
Absolutely. All programs can be delivered privately at your company or virtually for your team, customized to match your internal goals and structure.
Reviews
- Review:IAMM Internal Audit Maturity ModelIn recognition of their dedication and contribution supporting IKEA Saudi Arabia in arranging training programs during 2017 & 2018, looking forward for more development and exciting effort this year. On behalf of IKEA Saudi Arabia, THANK YOU!Dalal KutbiCountry Learning & Development Manager at IKEA Saudi Arabia
- Review:Certified Professional in Quality and Patient SafetyDating back to 2014, Mobily’s “LEORON” experience has grown from a single collaboration to a long-term partnership. We consider ‘’LEORON” Institute as a strategic partner, whose contribution has been nothing but premium in equipping our staff with field-based knowledge and information. Past three years have resulted with an expanded collaboration with superior customer service and support. Best Regards,Turki S. AlsahaanDir L&D, Talent Management at Mobily, Etihad Etisalat Company
- Review:Certified Professional in Quality and Patient SafetySince the partnership was signed between BAE Systems Saudi Development & Training and LEORON in 2017, we have been working together to offer the Saudi market a complete portfolio of training solutions benefiting from the wide and extensive experience of both parties. Recognizing the great success of this partnership, we are looking for further collaborations in the future that will position both companies as one of the leading training providers in Saudi Arabia. We thank the LEORON team for their full cooperation and continuing support, and look forward to further success together in the years to come.Emad AlrajihVP, Business Winning at BAE Systems Saudi Development & Training
- Review:Certified Professional in Quality and Patient SafetyWe have been working with LEORON for the past two years and will be working with them again this upcoming year. The programs they delivered were fruitful and exciting and our organization has received positive feedback from the participants. What our organization aims to do is to provide at no cost training for all the private sector employees so that they may benefit from our offered programs in Innovation and Professional development. Leoron has helped us achieve this goal. We look forward to continuing this service and wish them the best of luck. Regards,Nasser M. Al-SubaieSr. Program Officer, Enterprise Learning & Human Development at Kuwait Foundation for the Advancemen
