Have you ever needed to show percentage increases or variances between columns in Power BI? Well, here’s a method through DAX formulas to show it.
At first, the idea of using tables in Power BI may not be considered the glamorous flashy way to show data visualization. I would argue that the clean interaction and formatting make it a great way to display this information. Whatever your thoughts may be on it, when communicating with teams who want to see the numbers, tables are necessary, and Power BI can deliver.
So, why not revert to Excel and be done with it? Well, inthis case, I am importing several large datasets from Cubes, manipulating themin Power Query, and I want the ease of use with Power BI’s backend. Besides,there are still some additional visualizations I will plan to add later.
So, let’s get started. I will use a partial Adventure Works database for this demo. This way I can share the file with you and show you how I did it. If you want to follow along you can download the zip file here, and open the Begin version. The Finished version is there for you in case you get stuck.
Our Fact Internet Sales Table contains sales information including Sales Amount and Order Date. Order Year is taken from Order Date and gives us an easy way to access the Year within the Sales Table. Now we can write some DAX to create a column for sales within the years of interest. Sales exist from 2010 through 2014, however, the first and last years each contain only one month of data, so I didn’t include them in the report.
First we create a measure for Sales in 2011. Right Click on the FactInternet Sales Table and select New Measure
We are now ready to write a DAX expression inside the Measure. You can see the top bar is highlighted in blue and we can add what we want here. The first thing to know is we are naming our Measure, followed by and equals sign.
--Meausre Name Above
--Sum Sales for only a specific year
--Reference the Sales Field to Sum it
We now have Sales that occurred in the Year 2011. To confirm it, you should see the Calculator Icon next to a new measure in the FactInternetSales table like the image on the left.
We can do the same for 2012 and 2013 (notice I removed the comments from these formulas). Right Click on FactInternetSales and Choose New Measure again. Then type in this DAX expression:
I bet you can figure out how to add the Sales2013 measure.So, go and do that. Then we’ll add our Year Over Year percentage changes.
OK – now we are ready to add our three Year Sales Columns toa new Table in the Visualization section of Power BI. Insert a TableVisualization and then drag and drop the Sales2011, Sales2012, and Sales2013into the Values section. Adjust the Measures format to US Dollars and removethe decimal places. After these adjustments, the table should look somethinglike this:
Next, let’s add a new measure to create our percentagechanges:
Δ'12 = (FactInternetSales[Sales2012] - FactInternetSales[Sales2011]) / FactInternetSales[Sales2011]
I have used all sorts of unusual characters in my measurenames, and have not had a problem as of yet. Above is the delta symbol, and itmakes for a concise column header.
Add another Measure for changes from 2012 through 2013. Thenchange the format for both measures to percentage.
Lastly, we will add a Variance column. Add another measureand use this formula:
+/- = FORMAT(FactInternetSales[Sales2013] - FactInternetSales[Sales2012],"#,##0;(#,##0)")
This is a great use of the Format formula inside a measure.It ensures that negative values show in parentheses.
OK – now that we have all of our measures let’s add them tothe table and see what they look like all together:
We need to add a few more pieces to this to make the presentation worth looking at.
Let’s add a month row to see how changes occur each monthfor each year, and let’s narrow the scope to Bikes only.
First, add the English Month Name from the DimDate Table.Add it as the first column. As you can see, we are not looking at the months inchronological order, so let’s fix that too. Add another column from the DimDateTable, MonthNumberofYear. Make this the last column, and set the value as theMaximum Number in the field. This will return the month number.
Next, order the table by the MontherNumberValueOfYearAscending.
We don’t want to see the Month Number in our Chart, so wewill employ a little hack. First click on the Format Roller Brush icon on theVisualizations Pallette. Then, under both the Column Headers and ValuesSections turn off Word wrap.
Now that we’ve done that, we need to hide the Month NumberColumn. Select the edge of the MonthNumber column header and drag it to theleft until it is completely hidden.
Great! Now we have a table showing changes by Month for 2011through 2013. Let’s add a slicer, so we can interact a little more with ourresult.
From the DimProductSubCategory Table drag the first column “BikeCategories” onto the canvas and change the visualization type to Slicer (the filter icon). Now we can view Mountain Bike, Road Bike, and Touring Bike Sales throughout the period selected.
I am including a zip file with both an incomplete and a complete version. This way you can work on the file, and compare it to the finished version if you get stuck. Hope that helps, and will hope to post more Power BI related information next week!