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



The previous webinar outlined how a pivot table in Excel can be useful for summarizing and analyzing data. There are certain limitations with the regular pivot in data modeling and building complex business logic. And hence, we require something more powerful to bind multiple data tables together from various data sources without losing the simplicity of pivot table “drag-drop” features.


It is quite surprising that not many Excel users know the feature that has been part of the regular Excel since Office 2013. So, I thought to create awareness around this simple yet powerful feature present in Excel with the weekly webinar series. Following is the first part of the series:


Download sample data file: SuperStore Sales


Introduction & basic concepts


We briefly discussed the history and basic features of a PowerPivot and how it is different from a regular pivot table. This video also highlights the data modeling concepts supported with PowerPivot.



5 Useful Features of Excel Pivot Tables

Do More with Pivot Tables - Value Field Settings


How add a PowerPivot data model?


With a simple dataset containing five related tables, this video walks you through the steps on how to add and build a data model in Excel. We learn how to enable the PowerPivot feature, add tables to the data model, and create relationships.



Data Modeling with Power Pivot - Getting Started


Adding a DAX measure and data from other sources


DAX is a powerful language introduced with the tabular model of SQL Server Analysis Services. We use DAX to build complex business logic in Power BI, SSAS Tabular models, and PowerPivot. This video shows how to add a measure in the PowerPivot.


We can add data from multiple sources in a PowerPivot data model using the Get Data feature in the Data ribbon. It is quite helpful, as we can connect with numerous sources without importing data to Excel. Since it is not mandatory to load the data table in Excel to analyze, we can analyze large datasets with billion rows without facing the challenges of reaching the rows limit of an Excel sheet and increasing the file size.



Also, see:

Analyzing data with Pivots


In the next webinar, we will talk about introducing DAX into data modeling.

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


Standard deviation denotes how far data points are from the mean. It represents the average distance between the mean of the data set and the individual data points.


Higher the standard deviation, the higher the variation.


Application of Standard Deviation


After mean, the standard deviation is the most commonly used in various statistical tests. A few typical applications of standard deviation:

  • Understanding the spread of data and its distribution

  • Identifying special causes of variation in the dataset

  • Standardizing the dataset (calculating Z value)

When combined with the mean, standard deviation plays a significant role in performing various statistical analyses and tests.


Calculating Standard Deviation in Excel


In Excel, we have two formulas for calculating Standard Deviation:

  • STDEV.P(<array>)



  • STDEV.S(<array>)

















Where,

P stands for a population (contains complete data of the entire scope)

S stands for a sample (contains data of a segment of the entire scope)


Depending on the dataset we are dealing with (population or sample), we use the appropriate standard deviation formula.


Also, see: Sampling



Have you come across the following message while establishing a relationship between two tables and wondered what it means?


If yes, then read along.


This article is my effort to decode this in a simple possible manner.


Ambiguity means interpreting in more than one way. Or, in simple words, what is the appropriate choice? There is uncertainty.


In a data model, we use the relationship to propagate filters between two tables. Like any robust system, the Power BI data model requires a clearly defined path for the filter propagation.


Let us understand with examples.


Scenario 1


The simplest example of an ambiguity in a data model is establishing more than one relationship between the two tables.


There can be one active relationship between two tables (denoted by a solid line). All the other relationships are always inactive (indicated by a dotted line)


In the above example, Orders[Order Date] and Orders[Ship Date] is related to ftCalendar[Date]. The relationship between Orders[Order Date] and ftCalendar[Date] is active, whereas the other is inactive.


I have added the following measure to the model:


Order Qty = SUM(Orders[Order Quantity])

When we use the above measure in a visual and ftCalendar[Year], it returns total orders by Order Date, grouped by year.


Power BI is propagating the filter through the active relationship between the two tables (Date to Order Date).


Imagine if both the relationships between the Order table and Calendar table is active.

Which Order Quantity would the above visual be returning? Ambiguous.


Power BI simply doesn’t allow us to create any ambiguous scenarios for the data model to avoid the above scenario.


Scenario 2


Consider the following scenario:


Manager[City] is related to Target[City] (Status – Active)


Manager[City] is related to Customer[City] (Status – Active)



When we establish a relationship between Customer[Customer Segment] and Target[Customer Segment], Power BI does not allow it to be active.


It says an active relationship between Target and Customer table creates an ambiguous relationship.



For a moment, let’s imagine that the relationship between Customer and Target is active. In that scenario, there will be two paths available for the model to choose for the filter propagation when we apply a filter from the Manager’s table:



Power BI model avoids such ambiguous decisions for the filter propagation.


What is the alternative?


We can temporarily active an inactive relationship with the help of USERELATIONSHIP. USERELATIONSHIP temporarily activates the inactive relationship (defined in the formula) and deactivates others to remove ambiguity. For more details, please see:

DAX: USERELATIONSHIP


I hope it is making some sense now. Or is it still ambiguous?