Ebooks Amazon Free

FREE ALL EBOOKS ON AMAZON AND COURSES ON UDEMY- What books and courses do you need? We have them FREE for you...

Showing posts with label Power BI. Show all posts
Showing posts with label Power BI. Show all posts

16 January, 2020

Employee Turnover Dashboard – Power BI for HR

Employee Turnover Dashboard – Power BI for HR

Jack – The recruiting hamster

Meet Jack. He is a recruiter at East Coasters Inc. In the first quarter of 2019, so far 17 people in Engineering, 12 people in R&D, 9 people in Customer Care and 7 people in Finance have left East Coasters. Jack could only manage to replace 12 of them. What should he do?
Buy Panadol, lots of it.
Jokes aside, people in HR know very well that the recruitment hamster wheel must go on. But you know what makes the HR manager’s life a little better? If you know employee turonver looks, you can manage it better.
So on that note, let’s see how you can create an interactive, fun and useful Employee Turonver dashboard using Power BI.

Quick demo of the HR Turnover dashboard

Before learning how to create this, just take a look at this beauty.

Start with data

Typical staff recruitment and turnover data looks like this:
  • Employee details (name, designation etc.)
  • Where they work (department, branch etc.)
  • Date of join
  • Date of leaving
  • Reason for leaving
Let’s assume this is how our data looks like. We have two sets of it. One for recruitment and another for leaving.

Download sample data

Load data and transform thru Power Query

Now that we have our data, let’s load it in to Power BI workbook. Open Power BI, click on Get data and point to your employee data set (in this case, the data came from an Excel file, for you this can be a SQL query, Oracle database or angry data dump from a bored data analyst in IT)
While at Power Query, it is a good idea to split the data in to dimension and fact tables. The exact set of tables depend on your input data. In our case, I have created below tables.
  • Fact Tables
    • Recruitments data – called staff
    • Leavers data – called leavers
  • Dimension Tables
    • Branches – dBranch
    • Departments – dDept
    • Designations – dTitle
    • Gender – dGender
    • Calendar (generated thru Power Query List.Numbers function) – calendar
The process of creating these tables is fairly straight forward. If you are not sure how to make them from your source tables, watch the video at the end of this article.

Load data and Model it in Power BI

At the end of this process, load data to Power BI and link up tables. Here is my data model. Dimension tables are in the middle.
Data model - Employee turnover dashboard - Power BI

Create some measures

Now that our data model is ready, let’s dax. I meant Data Analysis eXpressions, you silly. You can measure and analyze recruitment and leaver data in any number of ways. Since Power BI allows us to interactively explore and visualize data, I find that even simple measures can deliver powerful results (as you will see in the dashboard).
Here are a few measures you can create:
(Refer to data model diagram above if you are not sure what a field refers to)
Leaver Count = COUNTROWS(leavers)
Joinee Count = COUNTROWS(staff)
Tunrover % = DIVIDE([Leaver Count], [Joinee Count], blank())

Total Staff to date =
CALCULATE(
[Joinee Count]-[Leaver Count],
FILTER(
ALLSELECTED('calendar'[Date]),
ISONORAFTER('calendar'[Date], MAX('calendar'[Date]), DESC)
)
)
While the above 4 measures are simple, the next one is a bit tricky. So if you dax with two left hands, then ignore the next one. You can still create powerful reporting.
The next measure tells us about top 2 branches and their contribution to overall turnover.
Top 2 branch leavers total = 
var t2 = topn(2,dBranch,[Leaver Count],DESC)
var t2_names = CONCATENATEX(t2, dBranch[Branch], ", ", [Leaver Count], DESC)
return
"Top 2 branches ("& t2_names &") account for " & format(divide(SUMX(t2, [Leaver Count]), CALCULATE([Leaver Count], all(dBranch)),0),"0%") & " of leavers"

Let’s get graphic

So our data is ready, measures are clicking. Time to place them in some visuals to see whats going on with our turnover. There are many options when it comes visualizing this kind of data. Just play with Power BI and keep what you like.
Here are a few options.

New Joinees vs. Leavers over time

Simple line chart with a text box for title. Uses [Joinee Count] and [Leaver Count] measures with Calendar[date] on horizontal axis.

Leavers by branch and gender

This next one is stacked bar chart with gender, branch and [leaver count]. We can then overlay a card visual with [top 2 branch leavers total] measure to see more info about top 2 branches.

Or a few cards with statistics

You can add multi-row cards to display statistics. When mixed with visual filters on relative date, you can get same measures in different context. See below for some inspiration.
Relative date filtering for the cards.

See top 10 designations of leavers

You can never go wrong with a black dress or good old fashioned table. A simple table of turnover % by job title (designation) will always look flash. But what if you have 100s of jobs. Simple, apply Top N filter and you can look at things that matter most.

Complete Turnover Dashboard

click to enlarge
Employee turnover  / attrition dashboard

Download Power BI workbook

Video tutorial – Employee Attrition Dashboard

If you are still not sure how everything works, check out this simple tutorial. Make sure you follow along in Power BI for best results. The video explains how to transform data in Power Query, how to generate custom calendar, how to create data model, measure development, visual selection and formatting. It is quite in-depth and yet not too long. Check it out below. Or watch it on my YouTube channel.

Are you HR + Power BI?

Do you work in HR and use Power BI? How do you measure and analyze turnover? Please share your thoughts and tips in the comments box. Even if you don’t work in HR, I am sure you find this example very useful for Power BI, Power Query and dashboard development.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

20 December, 2019

Commonwealth Games Performance – Power BI Visualization – Tutorial

Let’s take a look at the games data thru Power BI to understand how various countries performed.
Here is my viz online (or you can see a snapshot below, click on it to expand).
Looks good, isn’t it? Well, read on to know how it is put together.

Commonwealth Games Performance – Power BI Visualization – Tutorial

Step 1: Define goals for your visualization
Whenever you are making anything more than a bar chart (come to think of it, even bar charts need a bit of noodling before hand), it is prudent to spend time thinking what you want to accomplish with the visual.
For me the goals are:
  • Understand how various countries have performed in 2018, compare that to previous editions of games (say 2014, 2010 and 2006)
  • See which countries have improved their medal performance from last games
  • Understand how top 10 countries performed – which events they excel in
  • Prepare everything in less than 2 hours
I made a rough sketch of the visualization too. But I deviated quickly once I started playing with the data in Power BI.
Step 2: Gather the data
The data for this visualization came from 2 sources:
  • gc2018.com for 2018 games data
    • https://results.gc2018.com/en/all-sports/medal-standings.htm
    • https://results.gc2018.com/en/all-sports/medallist-by-sport-<country name>.htm
  • thecgf.com for previous games data
    • https://thecgf.com/results/games/3052 for 2014
    • https://thecgf.com/results/games/3046 for 2010
    • https://thecgf.com/results/games/3026 for 2006 medals
I mashed up most of the data in Power Query, but had to use a bit of Python (more on this in a future blog post) as the medalist by sport page (https://results.gc2018.com/en/all-sports/medallist-by-sport-<country name>.htm) has weird formatting with event name as A tag followed by medalists in a table and this was too much to process in PQ.
Step 3: Set up the data model
After gathering all the data in PQ, we can bring only relevant tables to Power BI model. I brought below tables:
  • medals – with medal tables for current (2018) and previous three editions of CG games
  • top 10 countries – event level medal data for top 10 countries in 2018
  • Countries – generated table with top 10 country names and their 3 letter abbreviations
  • medal types – typed in table with URLs for medal images and custom sort order of Gold, Silver and Bronze
Step 4: Create measures
Since one of the goals for this visual is to keep everything under 2 hours, I created only basic measures.
  • Medal Count = sum(medals[Medals])
  • Medal Count for 2014 = CALCULATE([Medal Count], 'medals'[Games] IN { "2014" })
  • Medal Count for 2018 = CALCULATE([Medal Count], 'medals'[Games] IN { "2018" })
  • Medal Count (all) = CALCULATE([Medal Count], all(medals[Games]))
  • Country Name = SELECTEDVALUE(medals[Country]) for showing in tooltip & chart header
  • % increase - 2014 to 2018 = DIVIDE([Medal Count for 2018]-[Medal Count for 2014], [Medal Count for 2014], 0) for showing in tooltip
  • medal count - top 10 = countrows('top 10 countries')
  • total medal count for country = CALCULATE([medal count - top 10], all('top 10 countries'[Event]))
  • medal % = [medal count - top 10] / [total medal count for country]
As you can see, these are basic arithmetic or simple CALCULATE measures. I used the excellent quick measure feature to create the Medal Count for 2014 measure and learned about IN keyword. #awesome
Step 5: Create visuals
Visual for exploring medal performance by country
I started with a simple slicer on games year and a matrix visual by country in rows, medal type in columns and medal count in values. Then I added data bars to the medal count.
Visual for exploring change over time:
Then I added Ribbon chart with Games, Medal Type and Medal Count to see how total medals have changed over time. When you pick a country from the matrix, this visual updates to show how that country’s performance changed over time.
Visual for seeing which countries improved in 2018:
I added a scatter chart with  Country as legend, Medal count for 2018 as X and Medal count for 2014 as Y. Then I added symmetry shading to this chart from analytics pane. Viola, we can see which countries did well or worse in this round compared to 2014.
Visual for tool tip
I inserted a new page (called Country Medals), changed the format to Tooltip and added a few visuals to make it a tool tip for the scatter chart.
Setting up tooltips is still painful, but this is a new feature, so I am sure MS will add more teeth to this power.
Linking scatter chart and tooltip
Select the scatter chart and from Format pane, set up tooltip to a report page and select Country Medals page.
Visual for seeing where top 10 countries excel
I added another matrix visual with Event in rows, abbreviated country name in columns and medal % in values. Then I added conditional formatting > Background color scales to spot bigger numbers easily.
This visual and the scatter plot are then linked to a slicer on medal type (Gold / Silver / Bronze) so you can see event performance and change over time for any type of medal.
Formatting the visuals
The default colors for visuals use Power BI color scheme. I changed the colors to match medals – Gold, Silver and Bronze so that they are easy to spot. Unfortunately, this would not sync across all visuals, so we have to format each of the visuals (well, only two – ribbon chart and bar chart on the tooltip page)

Download Commonwealth games Power BI Viz

Click here to download the workbook. Examine the query definitions (especially top 10 countries) to learn some quirky ways to work with Power Query. Enable interactions from view ribbon to see how each visual interacts with others. Play with it and mash up your own data to create something equally awesome. If you end up making another viz from this data, feel free to post it in the comments section so we all can see and learn from you.
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

19 December, 2019

Exam Ref 70-778 Analyzing and Visualizing Data by Using Microsoft Power BI

Exam Ref 70-778 Analyzing and Visualizing Data by Using Microsoft Power BI
DOWNLOAD

Description

Product Description

Prepare for Microsoft Exam 70-778–and help demonstrate your real-world mastery of Power BI data analysis and visualization. Designed for experienced BI professionals and data analysts ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level. 
Focus on the expertise measured by these objectives: 
  • Consume and transform data by using Power BI Desktop 
  • Model and visualize data 
  • Configure dashboards, reports, and apps in the Power BI Service 
This Microsoft Exam Ref: 
  • Organizes its coverage by exam objectives 
  • Features strategic, what-if scenarios to challenge you 
  • Assumes you have experience consuming and transforming data, modeling and visualizing data, and configuring dashboards using Excel and Power BI 

About the Author

Daniil Maslyuk (MCSA: BI Reporting; MCSE: Data Management and Analytics) is a Microsoft business intelligence consultant who specializes in Power BI, Power Query, and Power Pivot; the DAX and M languages; and SQL Server and Azure Analysis Services tabular models. Daniil blogs at xxlbi.com and tweets as @DMaslyuk.

Features & details

Product Details

  • Publication date: June 7, 2018
  • Publisher: Microsoft Press
  • Language: English
  • ASIN: B07DLCT6SC
  • Amazon.com Sales Rank: 125992

About the author

Follow the author to get new release updates and improved recommendations.
Daniil Maslyuk
Daniil Maslyuk is a business intelligence consultant and trainer with background in finance and sales in transnational corporations. Daniil is particularly passionate about Microsoft technologies.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

18 December, 2019

[Free ebook]High Impact Data Visualization with Power View, Power Map, and Power BI

[Free ebook]High Impact Data Visualization with Power View, Power Map, and Power BI
 DOWNLOAD


Description

Product description

High Impact Data Visualization with Power View, Power Map, and Power BI helps you take business intelligence delivery to a new level that is interactive, engaging, even fun, all while driving commercial success through sound decision-making. Learn to harness the power of Microsoft’s flagship, self-service business intelligence suite to deliver compelling and interactive insight with remarkable ease. Learn the essential techniques needed to enhance the look and feel of reports and dashboards so that you can seize your audience’s attention and provide them with clear and accurate information. Also learn to integrate data from a variety of sources and create coherent data models displaying clear metrics and attributes.
Power View is Microsoft's ground-breaking tool for ad-hoc data visualization and analysis. It's designed to produce elegant and visually arresting output. It's also built to enhance user experience through polished interactivity. Power Map is a similarly powerful mechanism for analyzing data across geographic and political units. Power Query lets you load, shape and streamline data from multiple sources. PowerPivot can extend and develop data into a dynamic model. Power BI allows you to share your findings with colleagues, and present your insights to clients.
High Impact Data Visualization with Power View, Power Map, and Power BI helps you master this suite of powerful tools from Microsoft. You'll learn to identify data sources, and to save time by preparing your underlying data correctly. You'll also learn to deliver your powerful visualizations and analyses through the cloud to PCs, tablets and smartphones.
  • Simple techniques take raw data and convert it into information.
  • Slicing and dicing metrics delivers interactive insight.
  • Visually arresting output grabs and focuses attention on key indicators.

What you’ll learn

  • Produce designer output that will astound your bosses and peers.
  • Drive business intelligence from Excel using BI in the Cloud.
  • Gather source data from corporate and public sources.
  • Integrate charts, maps, and tables to deliver visually stunning information.
  • Discover new insights as you chop and tweak your data as never before.
  • Adapt delivery to mobile devices.
  • Outshine competing products and enhance existing skills.

Who this book is for

High Impact Data Visualization with Power View, Power Map, and Power BI is written for any Excel or SharePoint user. Business Intelligence developers, power users, IT managers, finance experts, and more can use this book to outshine the competition by producing high-impact business intelligence reporting on a variety of devices from a variety of sources.

Table of Contents

  1. Self-Service Business Intelligence
  2. Power View and Tables
  3. Filtering Data in Power View
  4. Charts in Power View
  5. Advanced Charting with Power View
  6. Interactive Data Selection
  7. Images and Presentations
  8. Mapping Data in Power View
  9. Power Pivot Basics
  10. Extending the Excel Data Model Using PowerPivot
  11. Power Pivot for Self-Service BI
  12. Discovering and Loading Data with Power Query
  13. Transforming Data with Power Query
  14. Power Map
  15. Self-Service Business Intelligence with Power BI
  16. Appendix A: Sample Data

About the Author

Adam Aspin is an independent Business Intelligence consultant based in the United Kingdom. He has worked with SQL Server for seventeen years, and Business Intelligence has been his principal focus for the last ten years. He has applied his skills for a range of clients, including J.P. Morgan, The Organisation for Economic Co-operation and Development (OECD), Tesco, Centrica, Harrods, Vodafone, Crédit Agricole, Cartier, Alfred Dunhill, The RAC and EMC Conchango.
Adam is a graduate of Oxford University. He is a frequent contributor to SQLServerCentral.com. He has written numerous articles for various French IT publications. A fluent French speaker, Adam has worked in France and Switzerland for many years. He is the author of SQL Server 2012 Data Integration Recipes (Apress, 2012), and of High Impact Data Visualization with Power View Power Map, and Power BI (Apress, 2014).
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

[Free ebook]High Impact Data Visualization in Excel with Power View, 3D Maps, Get & Transform and Power BI

[Free ebook]High Impact Data Visualization in Excel with Power View, 3D Maps, Get & Transform and Power BI

DOWNLOAD

English | ISBN: 1484223993 | 2016 | 636 Pages | PDF | 30 MB
Take business intelligence delivery to a new level that is interactive, engaging, even fun, all while driving commercial success through sound decision making. Do this through the power of visualization using this updated edition covering new features and added support for visualization in Excel 2016, and describing the latest developments in Get & Transform and DAX. The example data set has also been updated to demonstrate all that Microsoft's self-service business intelligence suite is now capable of.

Data Visualization in Excel 2016: Power View, 3D Maps, Get & Transform, and Power BI, 2nd Edition helps in harnessing the power of Microsoft's flagship, self-service business intelligence suite to deliver compelling and interactive insight with remarkable ease. Learn the essential techniques needed to enhance the look and feel of reports and dashboards so that you can seize your audience's attention and provide them with clear and accurate information. Also learn to integrate data from a variety of sources and create coherent data models displaying clear metrics and attributes.

Power View is Microsoft's ground-breaking tool for ad-hoc data visualization and analysis. It's designed to produce elegant and visually arresting output. It's also built to enhance user experience through polished interactivity. Power Map is a similarly powerful mechanism for analyzing data across geographic and political units. Get & Transform lets you load, shape and streamline data from multiple sources. Power Pivot can extend and develop data into a dynamic model. Power BI allows you to share your findings with colleagues, and present your insights to clients.
Data Visualization in Excel 2016: Power View, 3D Maps, Get & Transform, and Power BI helps you master this suite of powerful tools from Microsoft. You'll learn to identify data sources, and to save time by preparing your underlying data correctly. You'll also learn to deliver your powerful visualizations and analyses through the cloud to PCs, tablets and smartphones.

Simple techniques take raw data and convert it into information.
Slicing and dicing metrics delivers interactive insight.
Visually arresting output grabs and focuses attention on key indicators.

What You Will Learn
Produce designer output that will astound your bosses and peers.
Drive business intelligence from Excel using BI in the Cloud.
Gather source data from corporate and public sources.
Integrate charts, maps, and tables to deliver visually stunning information.
Discover new insights as you chop and tweak your data as never before.
Adapt delivery to mobile devices.
Outshine competing products and enhance existing skills.

Who This Book Is For
Data Visualization in Excel 2016: Power View, 3D Maps, Get & Transform, and Power BI, 2nd Edition is written for any Power BI Desktop, Excel or SharePoint user. Business Intelligence developers, power users, IT managers, finance experts, and more can use this book to outshine the competition by producing high-impact business intelligence reporting on a variety of devices from a variety of sources.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1