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: