VA

Visual appearance of a histogram is affected by size of the bin so it is useful to overlay density plot over histogram as this curve is not affected by size of the bin and looks more like a smoothed version of histogram and is aesthetically pleasing

In this blog, I shall show how to create a normal curve over a Histogram in Power BI.

I want to create a bell curve that overlays a histogram

I am looking at age distribution of customer table of ContosoRetailDW database

First, I create a calculated column that calculates age of the customer

Age = floor (DATEDIFF ([Birth Date], date(2011,12,31), DAY)/365.25,1)

Then I create a calculated table called “Age Table”

Age Table = var tab = GENERATESERIES (0,100,1)

return ADDCOLUMNS (tab,”SNO”,RANKX(tab,[Value],,ASC))

Then I create a relationship between age columns of Age table and Customers

I need to create a Histogram with a line chart overlayed

Histogram is a Bar plot which has a numerical X axis or we can use bins. In my case I am going to create as Continuous x axis

Normal curve is a line chart but to get a Normal curve I need to calculate the Probability density function of age distribution (Normal Distribution)

I choose line and clustered column chart from visualisation pane in x axis

Before that I need to create two measures one for Histogram and other for Normal curve

Measure for Histogram is Customers

Customers = VAR cust = COUNTROWS(Customers)

RETURN IF (cust = blank(),0,cust)

Measure for Normal curve is

PDF =

VAR mean =

CALCULATE ( AVERAGE ( Customers[Age] ), ALLSELECTED ( ‘Age Table'[Age] ) )

VAR sd =

CALCULATE ( STDEV.P ( Customers[Age] ), ALLSELECTED ( ‘Age Table'[Age] ) )

VAR nd =

NORM.DIST ( FIRSTNONBLANK ( ‘Age Table'[Age], 1 ), mean, sd, FALSE() )

RETURN

IF ( nd = BLANK (), 0, nd )

First calculate the mean and then the standard deviation and plug this into NORM.DIST function

This will be the measure for the line chart

For the chart

X axis will be Age Table [Age]

For histogram, we use Customer’s measure

For line chart, we use PDF measure

Below is the result. We get a nice bell curve overlayed over a Histogram

You can start the bell curve from the start of the first histogram bar. Input data shows minimum age of customer as 31

To achieve the below graph, we just calculate age table like this

Age Table = var tab = GENERATESERIES(min(Customers[Age]),max(Customers[Age]),1)

return ADDCOLUMNS(tab,”Sno”,rankx(tab,[Value],,ASC))