book

Analyzing Data with Power BI and Power Pivot for Excel, First Edition

by Alberto Ferrari, Marco Russo

April 2017

Beginner to intermediate

256 pages

7h 54m

English

Microsoft Press

Read now

Unlock full access

Who this book is forAssumptions about youOrganization of this bookConventionsAbout the companion contentAcknowledgmentsErrata and book supportWe want to hear from youStay in touch
Working with a single tableIntroducing the data modelIntroducing star schemasUnderstanding the importance of naming objectsConclusions
Introducing header/detailAggregating values from the headerFlattening header/detailConclusions
Using denormalized fact tablesFiltering across dimensionsUnderstanding model ambiguityUsing orders and invoicesCalculating the total invoiced for the customerCalculating the number of invoices that include the given order of the given customerCalculating the amount of the order, if invoicedConclusions
Creating a date dimensionUnderstanding automatic time dimensionsAutomatic time grouping in ExcelAutomatic time grouping in Power BI DesktopUsing multiple date dimensionsHandling date and timeTime-intelligence calculationsHandling fiscal calendarsComputing with working daysWorking days in a single country or regionWorking with multiple countries or regionsHandling special periods of the yearUsing non-overlapping periodsPeriods relative to todayUsing overlapping periodsWorking with weekly calendarsConclusions
Introducing slowly changing dimensionsUsing slowly changing dimensionsLoading slowly changing dimensionsFixing granularity in the dimensionFixing granularity in the fact tableRapidly changing dimensionsChoosing the right modeling techniqueConclusions

Using data that you cannot aggregate over timeAggregating snapshotsUnderstanding derived snapshotsUnderstanding the transition matrixConclusions
Introduction to temporal dataAggregating with simple intervalsIntervals crossing datesModeling working shifts and time shiftingAnalyzing active eventsMixing different durationsConclusions
Introducing many-to-many relationshipsUnderstanding the bidirectional patternUnderstanding non-additivityCascading many-to-manyTemporal many-to-manyReallocating factors and percentagesMaterializing many-to-manyUsing the fact tables as a bridgePerformance considerationsConclusions
Introduction to granularityRelationships at different granularityAnalyzing budget dataUsing DAX code to move filtersFiltering through relationshipsHiding values at the wrong granularityAllocating values at a higher granularityConclusions
Computing multiple-column relationshipsComputing static segmentationUsing dynamic segmentationUnderstanding the power of calculated columns: ABC analysisConclusions
Understanding different scenariosMultiple source currencies, single reporting currencySingle source currency, multiple reporting currenciesMultiple source currencies, multiple reporting currenciesConclusions
TablesData typesRelationshipsFiltering and cross-filteringDifferent types of modelsStar schemaSnowflake schemaModels with bridge tablesMeasures and additivityAdditive measuresNon-additive measuresSemi-additive measures

Content preview from Analyzing Data with Power BI and Power Pivot for Excel, First Edition

Introduction

Excel users love numbers. Or maybe it’s that people who love numbers love Excel. Either way, if you are interested in gathering insights from any kind of dataset, it is extremely likely that you have spent a lot of your time playing with Excel, pivot tables, and formulas.

In 2015, Power BI was released. These days, it is fair to say that people who love numbers love both Power Pivot for Excel and Power BI. Both these tools share a lot of features, namely the VertiPaq database engine and the DAX language, inherited from SQL Server Analysis Services.

With previous versions of Excel, gathering insights from numbers was mainly a matter of loading some datasets and then starting to calculate columns and write formulas to design charts. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Beginning Power BI: A Practical Guide to Self-Service Data Analytics with Excel 2016 and Power BI Desktop, Second Edition

Dan Clark

Publisher Resources

ISBN: 9781509302833Purchase book

Analyzing Data with Power BI and Power Pivot for Excel, First Edition

by Alberto Ferrari, Marco Russo

Introduction

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Beginning Power BI: A Practical Guide to Self-Service Data Analytics with Excel 2016 and Power BI Desktop, Second Edition

Data Analysis with Microsoft Power BI

Power Query for Power BI and Excel

Excel Power Pivot and Power Query For Dummies

Publisher Resources

Introduction

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

Beginning Power BI: A Practical Guide to Self-Service Data Analytics with Excel 2016 and Power BI Desktop, Second Edition

Data Analysis with Microsoft Power BI

Power Query for Power BI and Excel

Excel Power Pivot and Power Query For Dummies

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.