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



I am a fan of data visualization. After a long time, the Power BI team has released a few significant updates related to standard visualizations available in Power BI Desktop. This article highlights three such features.

Grand Total in Stacked Charts

Stack charts help in displaying results by multiple dimensions in a visual.

The challenge had been with the visual that it could show the values by segment, but we can’t see the total



With this release, we can now display the total


It also adds the total in the tooltip:


Perform Arithmetic Operations in Q&A visual

Q&A visual is quite intuitive as we can ask questions in natural language, and it returns results. I have explained these features in detail in the following articles:

Power BI Visuals: Q&A

Power BI Visuals: Q&A (July 2020 Update)

In this update, we can perform an arithmetic operation (addition, subtraction, multiplication, and division) in the Q&A visual. For example, if I want to check what is 25% of my total revenue quickly, I can use the Q&A visual to get the answer:

In this case, Revenue is a measure.


We can perform arithmetic operations on measures as well:


It follows the BODMAS rule

Get Insights from Smart Narrative

Want to get the insights from a visual quickly?


It is a two-step process now: Right-click > Summarize

We can add this as a visual, and it provides a summary of all visuals present in the page.

It is the latest visual in the arsenal, and currently is a preview feature


Visit the link below for more details on Smart Narrative:

Microsoft docs: Smart Narrative

#vivran



Pareto chart is a visual representation of the Pareto Rule.


Pareto Rule, which is also popularly known as 80/20 rule, was coined by Italian mathematician Vilfredo Pareto. He stated that 20% of the population owns 80% of the land.


Later, the quality guru Joseph Juran changed to 80% of the problems is due to 20% of the reasons.


In simple words, the Pareto rule is all about prioritizing the vital few from the trivial many.

This video shows the steps involved to create a Pareto chart in Excel.

Related articles:

https://www.vivran.in/post/do-more-with-pivot-tables-value-field-settings


@imvivran

#vivran




In the earlier article, we learned how to calculate the moving average at a day level (30 days). It is since the date granularity is at a day level. This article explains how we can calculate the moving average at a month or a quarter level when the date granularity is at a day level.

We have included a calendar table in our data model:

And created a relationship with the Order table:


Unlike the earlier example, we cannot use the AVERAGEX formula due to the granularity. We use the mathematical formula for average (sum of numbers divided by count of numbers).

Moving Average by Month

Let us say we are calculating the moving average of the past three months. For this, we sum the revenue for the previous three months and divide by the month count (3).


To calculate the count of the month, we use the formula DISTINCTCOUNT on the month column of the calendar table.


1. Moving Average _M = 
2. VAR _CurrentDate =
3.      MIN ( ftCalendar[Date] ) - 1
4. VAR _FilterDate =
5.      DATESINPERIOD ( ftCalendar[Date], _CurrentDate, -3, MONTH )
6. VAR _Monthly =
7.      CALCULATE ( [Revenue], _FilterDate )
8. VAR _MonthCount =
9.      CALCULATE ( DISTINCTCOUNT ( ftCalendar[Month] ), _FilterDate )
10.VAR _Average =
11.     DIVIDE ( _Monthly, _MonthCount )
12.RETURN
13. _Average


Moving Average by Quarter

Similarly, to calculate the moving average of the last three quarters, we replace month components by the quarter


1. Moving Average _Q = 
2. VAR _CurrentDate =
3.      MIN ( ftCalendar[Date] ) - 1
4. VAR _FilterDate =
5.      DATESINPERIOD ( ftCalendar[Date], _CurrentDate, -3, QUARTER )
6. VAR _Quarterly =
7.      CALCULATE ( [Revenue], _FilterDate )
8. VAR _QuarterCount =
9.      CALCULATE ( DISTINCTCOUNT ( ftCalendar[YearQtr] ), _FilterDate)
10. VAR _Average =
11.     DIVIDE ( _Quarterly, _QuarterCount )
12.RETURN
13. _Average


Download sample file


@imvivran

#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