Sometimes when your working on a line chart you want the x-axis to stay centered on a chart. This tutorial will walk you through how to create an X-Axis that will always center it’s self on the graph.
Video Support Material:
The measures discussed within this tutorial are:
Variance All = SUM(Sales[Variance])
The column name Variance is found in the data table called sales. This is just a numerical column.
After summing up all the variances we can calculate the min and max lines.
Const Max Line = [Variance All] * 1.2
Const Min Line = -1 * [Const Max Line]
Finally to calculate the variance to date you can use this filtered measure, which will only produce historical values.
Variance To Date = CALCULATE([Variance All], FILTER('Sales','Sales'[Date] <= EOMONTH(TODAY(),0)))
Thanks for watching our short tutorial. If you like this video please be sure to follow me (Seth Bauer) on Twitter, LinkedIn and be sure to subscribe to the PowerBI.Tips YouTube channel.
This is part 2 in the 3 part series on developing super cool tables using some fancy measures. In part 1 we walked through how to build a table that uses an un-pivoted data source in the Query Editor. This technique allows you to change the types of categorical values in a table. If you missed part 1 and want to get caught up follow this link. Now, continuing with the series, Part 2, we will build the supporting materials (Selector Table, What If Slicers, and measures) for the report.
Once we are done the final product will look like the following:
Part 2… Go.
To make sure we are starting off on the correct step. We left off part 1 when we had completed a Pivoted Data Table and included an Attribute Slicer that would allow us to toggle between the Manager and Region Categories. Your table should look like the following diagram: (If you don’t have this you might want to start with Part 1 found here)
Pivoted Data Table
Note: I have also included a Slicer which is used with the Attribute field.
Next, we will need to add a table that will allow us to use the SalesReps, PercentChange, and Margin column headers in our report. On the Home ribbon click Edit Queries, then select New Source on the Home ribbon. In the Get Data window select Blank Query, click Connect to proceed. Open the advanced editor by clicking the Advanced Editor button found on the Home ribbon. Enter the following M code into the Advanced Editor:
let
Source = #"Pivoted Data",
#"Kept First Rows" = Table.FirstN(Source,1),
#"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Attribute", "Value", "UniqueID"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"})
in
#"Removed Columns1"
Click Done to close the Advanced Editor. Rename the table to Selector. When you are finished your table should look like the following:
Create Selector Table
Click Close & Apply on the Home ribbon to close the Query Editor. Add a slicer with the following selections:
Add Selector Attribute Slicer
Now, we want to detect which of the Attributes have been selected from this table. We can accomplish this by creating a measure using the DAX function SELECTEDVALUE. Right Click on the table named Selector and from the drop down select New Measure. Enter the following DAX equation:
rankBy = SELECTEDVALUE(Selector[Attribute])
In addition to the knowledge of which column was selected from the selector table, we will also want to detect to make sure at least one categorical value has been selected. The categorical values we are talking about were generated earlier. The values could be either the Manager or Region values of the Attribute column in the Pivoted Data table. Using the ISFILTERED DAX function enables this section. Add the following measure to the Pivoted Data table:
Next, we need to gather some user input in the form of a number from 1 to 10. To input this information we need to produce a What If Parameter. On the Modeling ribbon click New Parameter in the What If section of the ribbon. Enter the following information into the What-if parameter dialog box:
What If Parameter
Note: Don’t forget to change the Name of the parameter.
Click OK to proceed. Power BI will automatically produce a measure table, a measure and a slicer on the report page.
Slicer Produced by What-if
Note: By default there is nothing selected in the box. However, you can adjust the slicer and a number will appear within the value box. You can also type in a number between 1 and 10 to the box to adjust the value.
This is where we go crazy with DAX. This portion of DAX is where all the magic happens.
We start off by building our totals measures. Place all these measures in the Pivoted Data table.
Total % Change = MAX( 'Pivoted Data'[PercentChange] )
Total Margin = SUM( 'Pivoted Data'[Margin] )
Total SalesReps = SUM( 'Pivoted Data'[SalesReps] )
These will be used repeatedly in our next group of DAX formulas.
The following measures will produce a calculated ranking for each numerical column. OK, Pause, This part really excites me here because the next few measures are where the magic happens. Pay close attention to what is happening here. Un-Pause, by using the DAX Switch function we can dynamically tell Power BI to adjust which column we want to see ranked by the top items. For example, if we select SalesReps in our attribute slicer. The following measures will automatically rank all the items in the table by the column named SalesReps. Thus, the items with the highest counts of SalesReps will be listed first. When you select Margin, the table will automatically adjust and re-rank the items by the Margin column. This is being done in the switch statement. For each column we are calculating custom rankings and then hiding or replacing values with the Blank() DAX function to not show items we don’t want.
Enter the following three measures into the Pivoted Data Table:
Whew, that was a ton of measures. All the key components are complete now. In part 3 we will clean up our report page and make it shine. I hope you enjoyed this tutorial. Also, follow me on Twitter and Linkedin where I will post all the announcements for new tutorials and content.
In many reports we produce we often need a method to score or rank data. For example, we may need to list the sales totals for the sales team and rank them from highest sales to lowest sales. Ranking can be done as a calculated column, or as a measure. When using a measure, the ranking becomes dynamic and takes on the filter context of the table, or visual, that is showing the data. Calculating a rank as a measure can be useful if you want to allow the user to select different categorical values such as product type and then have the report automatically rank the selected items. When the report filter context changes the items are automatically re-ranked.
Alright, let’s jump into the data!
Open PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. On the View ribbon click the Advanced Editor button. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.
Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
let
Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2017/05/Clothing-Sales.xlsx"), null, true),
ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"
Once you have copied the m code above into the query editor click Done.
Clothing Sales Data
Be sure to name your query Clothing Sales. Then on the Home ribbon click Close & Apply to load the data into the data model.
To understand how the ranking will work we must first understand the DAX function ALLSELECTED. You can read more about the Microsoft documentation on this function here.
To illustrate how the ALLSELECTED() function works we will make two measures and place them in a simple table.
Begin by creating a sum of the Sales in the Clothing Sales table. Click New Measure on the Home ribbon. Enter in the following measure equation:
Total Sales = SUM ( ‘Clothing Sales'[Sales] )
Now, create a Table visual with the selected columns shown in the image below.
Add Table Visual
Sweet, we can see that all the categorical items have been added together forming totals. Add the Slicer visual for the Category column, see example below.
Add Slicer
Once the slicer is added we can select various items and see our table filter correctly.
Using the Slicer
Note: if you want to select multiple items in the slicer, hold the ctrl key and click on the multiple items that you want to select. This is how I selected the multiple items in the image above.
Now, let us make a measure doing the same calculation but this time we will apply the ALLSELECTED() DAX function. Click on New Measure on the Home ribbon and enter the following DAX formula.
In this new formula we are calculating the sum of all the clothing sales but using the filter context of all the items selected from our filters. Notice with nothing selected in our slicers that the sum of all Total Sales 55k, is the same for each row of the table for the column Total Sales ALLSELECTED. This is due to the fact that we changed the filter context for the sum calculation.
Select Jeans and Pants from the slicer. Notice we have the same results but with different totals. The totals calculated using ALLSELECTED ignored the filter context of jeans and pants and calculated the total of all the selected sales.
Select Jeans and Pants
Finally, we will now add the Ranking. To calculate the rank we use the DAX function RANKX(). More documentation can be found on RANKX here.
Add the new measure, Ranking, to the table visual. Ta Da, automatic ranking based on information that was selected from our slicer visual.
Adding Ranking Measure
Note: when we used the RANKX function we called out a specific column the Category column from our Clothing Sales table. If you only specify the table name this measure will not work. We are using the filter context of the categories to conduct the ranking operation.
Every so often you find a hidden gem, something so valuable, just hiding there in plain sight. Recently, I found out that I LOVE the using Variables with in a DAX expression. Ok, brief introduction, I was trying to calculate sales changes between different years. The data looked similar to the following table:
Year
Month
Sales
2013
1
20
2013
2
25
2014
1
22
2014
2
23
2015
1
21
2015
2
31
I wanted to sum the sales for each year and compare the percent change between each year. If you have followed my blog for a while, I have a ton of tutorials about percent change. ( See list of other tutorials dealing with percent change )
Now, for the twist making this calculation more difficult. I want to calculate percent change even when a filter is applied by clicking on a visual. By using variables we can apply some data modeling voodoo magic to get the desired results. See a sample of the measures working in the Power BI Embedded sample below:
Let’s dive in!
Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. On the View ribbon clickthe AdvancedEditor button. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.
Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
let
Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2017/05/Clothing-Sales.xlsx"), null, true),
ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number)
in
#"Inserted Year"
Your loaded data should look like the following:
Load ClothingSales Data
Rename the Query to ClothingSales, and click Close & Apply to load the data into the data model.
It is a good practice to review the data columns and verify that the formatting is set up correctly. To do this click on the the column Date in the Fields pane, then click on the Modeling ribbon. In the Formatting section change the format to Date Time and select M/d/yyyy format. Repeat this for the following columns:
Format Sales to Currency then select $ English (United States)
Format Year to Whole Number
With our data properly formatted we are ready to build a simple bar chart. See the image below for the bar chart set up:
Create Bar Chart
To start we will create a explicit calculation for the total selected sales. On the Home ribbon click the New Measure button and enter the following code:
Total Selected Sales = SUM(ClothingSales[Sales])
Pro Tip: To learn more about explicit vs implicit calculations within Power BI Click the following link.
Add a Card visual and add our new measure.
Add Card Visual
Let’s change the formatting measure so we can see the full number. With the Card still selected click the Paint Roller expand the section labeled Data label and change the units from Auto to None. This allows us to see the entire sales number.
Remove Number Formatting from Card
Let’s enhance this card visual further by adding dollars formatting. Make sure you have the Total Selected Sales measure selected by Clicking on the words of the measure. Then, click on the Modeling ribbon click and change the Format to Currency, then select $ English (United States). The final format of the card visual should look like the image below:
Final Card Format
For the next step we will repeat the previous steps for our new measures.
Create the measure
Change the formatting of the card
Change the formatting of the measure
The next measure will calculate the prior year total sales, but only calculate this value when a filter context from a different visual is applied. Take note this is the magic I was talking about!!
Prior Year Sales =
VAR
selectedYear = if( HASONEVALUE(ClothingSales[Year]),
FIRSTNONBLANK(ClothingSales[Year],ClothingSales[Year]),
BLANK() )-1
Return
CALCULATE(
SUM(ClothingSales[Sales]),
ALL(ClothingSales),
ClothingSales[Year] = selectedYear)
I am going to pause a second here and explain what is going on in this measure as we have multiple things going on. First, we are creating a variable with the VAR. In the below image I show you that the variable that we are defining is called selectedYear. SelectedYear returns a value of blank if multiple years are selected. This is accomplished by using the if statement and the HASONEVALUE() DAX formula. The Return in then allows us to output a value. In this case we are using the CALCULATE() to sum all the sales. The next part is crucial to making this work. Within the Calculate we are applying two filters. The first filter selects the entire table with the All() then we apply the filter from the earlier defined variable with the name of selectedYear.
Prior Year Sales in Color
Apply the same formatting steps 2, and 3 mentioned earlier. Now, we can select one of the years within our bar chart. Doing so populates the two measures we created. The selected year total sales, and the prior year of sales. I selected the year of 2014, which has sales of $11k. Hover your mouse over the bar for year 2013 and the tool tip will appear showing a total sales of $10K.
Selected Bar
Now we will make our percent change measure. Enter the following as a new measure:
Percent Change = DIVIDE([Total Selected Sales],[Prior Year Sales],1)-1
Pro Tip: Use the DAX function DIVIDE when dividing two numbers, this protects against odd cases where the denominator is zero.
Click on the Modeling ribbon and change the formatting of the measure to Percentage. Add a final measure that calculates the difference. See measure below:
Prior Year Delta = [Total Selected Sales] - if( [Prior Year Sales] = BLANK(), [Total Selected Sales],[Prior Year Sales])
This measure calculates the total changes in sales between the two selected years. The if statement at the end of the equation handles a special case that if you don’t have any individual year selected the prior year delta defaults to zero.
Thanks for following along an learning about variables. Trust me, these are super helpful. Make sure you spend some learning how to leverage variables in your DAX equations.
This Tutorial was inspired by the following article I found from SQLBI authored by the master of DAX Alberto Ferrari. I mean, come on, Ferrari, how baller is that last name! I guess that is why he is the master. Kudos to you Alberto!
Still need more reading about DAX, check out the following books:
