Calculate variance percentage in Power BI

Today we create variance percentage change calculation. In our data set we will calculate Revenue using DAX and find out previous month Revenue and check percentage variance for both measures.

To demonstrate we have load Calendar, Product and SALES table in Power BI Desktop:

 

Calendar table:

Product table:

Sales table :

Relationship between table:

Add a calculated column “Start of month” in Calendar table:

Now calculate Revenue using DAX formula. We have product price in ‘Product table’ and order quantity in ‘SALES table’.

We will use SUMX function and to fetch another column from different table (i.e. Product price) will use RELATED function

Select New Measure > type formula in formula bar:

 Revenue = SUMX(Sales,Sales[OrderQuantity]*RELATED(Products[ProductPrice]))

Now calculate previous month Revenue:

Prev Month Revenue = CALCULATE([Revenue],DATEADD(‘Calendar'[Date],-1,MONTH))

Select Table visualization> drag SOM field from Calendar table:

Select Revenue and Prev Month Revenue field in visualtions:

Calculate percentage change for Revenue w.r.t. Previous month:

% Change Revenue = DIVIDE([Revenue],[Prev Month Revenue]blank())-1

Change Format for measure

Select % change Revenue field

Create a Bar chart to plot percentage change in Revenue for month:

Format visualization:

Now we calculate variance for Quarter:

Add a new measure to calculate Prev Quarter Revenue:

Prev Quarter Revenue= CALCULATE([Revenue],DATEADD(‘Calendar'[Date],-1,QUARTER)

Calculate variance for Quarter:

% Change Qtr = DIVIDE([Revenue],[Prev Quarter Revenue]blank())-1

Create a Bar visualization and format chart:

Leave a Comment

Your email address will not be published. Required fields are marked *

Trustpilot
Scroll to Top