Experience is a great teacher. And in my experience, the beginning has always been tough.

As a BI consultant & trainer, I have worked with various people: from the frontline employees to CEOs. Our interactions gave me great insights into the multiple challenges they face.

I have put together a few pages from my experience and learning; covering Power BI, Excel, Power Query, Power Pivot, Tableau, data analytics & data visualization.

The purpose of the blog is to get you started.

I hope you’ll find them helpful.

Vivek



Pivots make the heavy lifting of summarizing and analyzing data simpler. It is easy to start with and has some useful features which simplify the slicing and dicing of data and view the performance from multiple perspectives. In this webinar, I covered a few essential aspects which can get you started with pivots in no time.


You can download the practice file from here: Sample Data file


The current demonstration is happening on iOS instead of Windows; hence you may find some of the features presented differently. However, the functionalities and the feature’s name are common among both the operating systems.

The webinar is in six parts for convenience.


Part 1: Getting Started with Pivots


Start analyzing a large data table in no time. Add interactivity to your table using slicers and calculated fields.


Windows users may also refer to the following article for recommended pivot table settings:


3 Pivot table settings you should use



Part 2: Descriptive statistics with Pivots


The default calculation of any numerical column in a pivot table is a sum, and for a text column, it is count. However, using the pivot table, we can calculate average, min, max, standard deviation. All of these metrics are part of descriptive statistics.


Related article:

Statistics Simplified: Central Tendency

Statistics Simplified: Variation



Part 3: Pivot table options & layered analytics


Adding layers to a table and allowing users to drill down and analyze performance from multiple perspectives is a useful pivot table feature.




Part 4: Calculating percentages in Pivots


Percentage calculation is a vital aspect of any analysis. It helps in creating a baseline for comparing different metrics or periods. It is quite simple to calculate percentages in a pivot table using value filed settings



Related article: Do More with Pivot Tables - Value Field Settings



Part 5: Groups & Slicer settings


Create additional categories using numerical fields with the grouping feature in the pivot table. These groups add another dimension to understand performance.




Part 6: Adding visual experience.


Visual analytics is an essential aspect of any analysis. Learn how to create an interactive dashboard-like experience using conditional formatting to generate heat maps, sparklines to depict trends, and more.




Also, see:

5 Useful Features of Excel Pivot Tables

Pareto Chart in Excel (vivran.in)

Say Hello to PowerPivots


In the next webinar, we will introduce Power Pivot, where we can use pivot tables with multiple tables and use relationships between the tables.


Join the WhatsApp group BI Simplified to ask questions, share best practices, and get notifications for future webinars.




Migrating data sources at times can be tricky in Power BI. This article focuses on how to change the data source from an Excel table to a SQL table in minimum possible steps, considering the following scenarios:

  • The name and structure of the data source tables are different in Excel and SQL. It includes a change of column names.

  • Migrate and apply all the data transformation steps.

  • The migration should not affect the existing data model and the visuals present in the report.


To achieve this, we require to ensure three points:

  1. Column names remain unchanged,

  2. Replicate all the data transformations under Applied Steps,

  3. Query Name remains unchanged.


For our example, we are changing the data source of the Product Subcategory table from Excel to SQL:


Excel table structure:


SQL table structure:



Step 1: Bring the data from SQL Server using the native query.




It ensures that we have the same column names:


In case you can't write the native query to change the column name, then add a step in Power Query to change the column names.


Step 2: Replicate the data transformation steps using Advance Editor


This step is the trickiest of the three but not difficult.


Step 1: Copy the data transformation steps from the Advance Editor of the Excel table


Select the query > Home > Advance Editor and copy steps, which include the data transformations. In this example, we have applied two transformations:



We can find these steps in the Advanced Editor under the same names:


Select & Copy the M code from the Advance Editor


Step 2: Paste the copied code from the previous step to the SQL query Advance Editor


Add a comma after the last line, and paste the copied code:

Paste the code in the SQL query's Advance Editor after adding a comma(,)

Update the reference of the previous step. In this case, the name of the last step is “Source”.



Step 3: Delete the Excel Query and Rename the SQL Query


Delete the Excel query and assign the same name to the SQL query:


And this should do.





Statistics Simplified is the series to express statistics in layman terms.


Let us start with an example. Below is the year-over-year sales data, along with the %age change from the previous year:







What is the average %age change from the year 2014 to 2019?

If we calculate the arithmetic mean (aka average) of the % Change, we get 9.6%. It means, each year, there is a 9.6% change from the previous year.


If we apply a 9.6% year-over-year change, we get a different result than 3682 for the year 2019.

There is a difference of 14 units or 0.39%.

It signifies that arithmetic mean or simple average is incorrect in such scenarios.

Geometric Mean for calculating an average of percentages

The geometric mean is applicable in such scenarios. The geometric mean of the above example is 9.5%.

When we apply 9.5% of change from the year 2014, we end up with 3,682 sales in 2019.

In Excel, we use the function GEOMEAN. Just like the function AVERAGE, it takes an array as an input. It requires one adjustment in the formula:

GEOMEAN (1+ <array>) - 1


Adding 1 to the range and then subtracting it offsets significant fluctuations and negative values.

Let us see one more example:










Here we see a significant difference between the average (arithmetic mean) and geometric mean (-3.2% vs. -9.9%).

Following is the difference when we apply this to validate the results:

The geometric mean is used in calculating the overall return% on stocks or investments over

time.


Also, see:

Mean vs. Median

Trim Mean



#vivran

Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING