Creating And Publishing Interactive Dashboards With Excel ...

1 Creating AND Publishing Interactive Dashboards WITH Excel POWER PIVOT, POWER BI, AND SHAREPOINT ONLINE CAIR 2016 Annual Conference -WorkshopKen Nelson MD, MSLoma Linda is a Workshop Import data to a data model in Excel create a dashboard in Excel Power Pivot Export to SharePoint Import a data model into Power BI create a dashboard in Power BI Publish Power BI to a URL that anyone can viewOur Task TodayEverything is Free Wouldn t that be awesome? Actually at this workshop the software is. Really, everything you see being used here today (for an educational institution) is FREE!

2 !!Content for this Workshop Data files Dataset Dataset Step by step guide being built today are basic but provide the foundation for you to develop complex reports based on your CreativeSample -Program Review DashboardSample - Dashboards with Hyperlinked TilesSample -Financial Aid Funds are Purpose SearchableSample -Application Metrics DashboardToday s dashboard - Excel Power Pivot ExampleToday s dashboard -Power BI ExampleBased on an Underlying Data ModelLet s Start by Opening ExcelMake sure Power Pivot has been enabled!Import DataMultiple data sources can be encountered are: SQL Excel TextWe have successfully imported the data fileRepeat Steps to Import the 2ndData FileWe have now imported two datasets: Census DemographicsWe can join these two tables on a column.

3 One column needs to be a unique JoinsDrag one column title to the otherThe indicator tells us we have established a one to many relationship, the arrow shows the s add a column to our census table based on a column from the demographics tableAdding a New ColumnIntroducing Data Analysis Expressions DAXP owerful language for Power Pivot and Power BIWhen we start to type suggestions are displayedComplete DAX Formula=RELATED(Demographics[Race_Ethnic ity])Our New Column Has Been CreatedLookup Table ExampleLookup tables support requests like listing students in a particular region while retaining multivariate filtered a slicer, create the list.

4 This can then be used in another report keyed on zip code to filter the DashboardSelecting the Location for ChartsSelecting Content for ChartWe will drag Student ID to Values and Academic Year to AxisSum doesn t work for our purpose, we want a distinct count of student IDFor this display it would be nice to use a line chart for showing the trend instead of a bar chartWe can also add a trendlineFormatting the TrendlineThe trendline can be formatted to look different from the data lineThe trendline can forecast forward on periods. You also have the option to add the equation and R-squared with Trendline and Default RangeFormatting axis by selecting minimum and maximum rangesThe Design Menu Offers Multiple Choices for Chart StyleBy Highlighting the Title We Can Add Our Own TextDepending on preference charts can look cleaner by removing field Filters Slicers When adding a filter you should select which visualizations are to be impacted by the is selected under Report Connections as shown on the Enrollment Bar ChartBy adding academic year to the axis it allows the year

5 To show when selecting different years. Be sure to set student count to distinct. Vary Colors in the Bar ChartAdding the Pie Chart for Academic LevelAdding the Race/Ethnicity ChartDashboard should now look something like thisYou can also save your file to a local to SharePoint Location depends on your Office 365 configurationIdentifying the URL for your SharePoint file is easy Click on Save As and copy the address in the barExcel Services Running in SharePointBuilding a dashboard in Power BI DesktopBuild in the desktop app FREE publish online -FREEL ocate your Excel file this is what I named mine.

6 Here s our data model from ExcelRemember the DAX formula we wrote in Power Pivot it s here too!The joins are still intact as well. Even the familiar indicator is present. Our canvas where we will start building our Power BI DashboardWAIT! I liked my Power Pivot dashboard don t make me start over!Report/ dashboard Tables JoinsThese are the available visualizations. You can also dl custom ones as problem! Export from Excel to Power BISlicers still work!By using Power BI you will have access to a wide variety of Interactive visualizations and more continue to be addedWe will do just as we did in Excel adding the Academic Year to the Axis and a distinct count of Student ID to the ValuesCreating the Line Chart select the line chart iconSetting Range on the ChartTip to get the visualization looking rightAdding Enrollment ChartJust drag columns to the axis to enable drill downOverall values then drill down to each school.

7 We could continue to do this for as deep as our dataset Race/Ethnicity ChartAcademic Level Pie ChartDAX to do this is:Location = [Zip Code] & "," & [Citizenship]Adding a Citizenship MapAdding FiltersConfiguring Filter InteractionsAdding a New Column Concatenate and Add Text if Desired DAX Statement to Concatenate Easy as & Adding a TitleColor choice is in Hex use color picker in Excel to see RGB and use an online converter to get Hex equivalentBringing it All TogetherWe ve added some titles still more to add belowFormatting AlignmentReady to PublishSelect File, Export.

8 Publish to Power BISuccess Now We Need a URLP ublish to Web URL or Embed CodeMAJOR CAUTIONURL and Embed for the dashboard Filter controls Drill Down Titles can be content sensitive Import Custom Visuals Experiment / Share / Join a user group Learn some Data Analysis Expressions (DAX) Use R? Power BI has built-in supportTips:When we use CALCULATE in an expression it allows us to override a our example we will use it to create a percent of students in an age filtered by Age Group in a given context, , YearUsing CALCULATE to capture all students in the filtered context -YearCalculate Age Group = CALCULATE([Distinct Count of Student ID],ALL(Census[Age Group]))DAX CALCULATEC alculate Age Group = CALCULATE([Distinct Count of Student ID],ALL(Census[Age Group]))MatrixPercent of Students = DIVIDE([Students],[Calculate Age Group])Students= DISTINCTCOUNT(Census[Student ID])

9 THANKS FOR ATTENDING!Contact Info:Ken Nelson