Category Archives: Power BI

Tons of Updates to Power BI in October!

There were so many updates to Power BI this month I thought I’d put all the links to the information on the updates in one place so we can get caught up quickly on everything that changed with the tool this month. Down below you’ll find my commentary on which updates I’m most excited about and links to the Power BI blog to get all the information on all the changes!

New to Power BI? Start here.

The big one here, in my opinion, is the ability to use custom visuals! Not only can you create your own visualizations to meet your organization’s needs, you can download other custom visualizations that have been shared in the Power BI Visuals Gallery. Some of these custom visuals are just awesome. The capabilities of this tool are off the charts (see what I did there?)!

Continue reading Tons of Updates to Power BI in October!

10 DAX Calculations for your Tabular or Power Pivot Model (Part 2)

This is part two of my 10 DAX Calculations blog series. In this series I’ve blogged ten different DAX calculations you may find useful in your Power Pivot, Tabular or Power BI model. So if you missed part one, I would encourage you to check that out.

Read 10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

So here’s five more DAX calculations (in no particular order) that I hope you will find useful. Enjoy!

5. DAX Use an Inactive/Custom Relationships in a Calculation

In Tabular and Power Pivot models, you are limited to only one Active relationship between two tables. Often in a relational database you’ll have a role-playing table or a table that is related to another table with more than one foreign-key. A common example of a role-playing table is a Date dimension table since our fact tables often have multiple dates, which is what the following screenshot depicts. We can see that there are two relationships between the Sales and Date tables.

image

The solid line indicates that this is an Active relationship. The dotted line indicates that relationship is Inactive. In the Manage Relationships window, you can clearly see that one of the relationships is Inactive (highlighted in red). What this means for us is that during slicing, dicing and filtering the Active relationship is used.

The good news is that we can still build calculations that leverage Inactive or unspecified relationships in our model! To do this we can use the USERELATIONSHIP function. This function allows us to specify which columns to relate two tables together.

Here’s an example of a calculation for Total Quantity that uses the relationship highlighted in red in the above screenshot:

Total Quantity by Update Date:=CALCULATE([Total Quantity],USERELATIONSHIP(Sales[UpdateDate],'Date'[Datekey]))

 

So when we slice the Total Quantity by Update Date calculation by the Date table, the relationship used is the not the default Active relationship as is the case with the Total Quantity measure. We can clearly see this illustrated below in the pivot table:

image

4. DAX Semi-Additives Measures Opening Period and Closing Period Calculations

Sometimes we have a requirement to create a semi-additive type calculation. If you’re familiar with creating SSAS multidimensional cubes, you’re probably familiar with the concept of semi-additive measure. A semi-additive measure is a measure that is not fully aggregate-able meaning that to find the correct measure amount we cannot aggregate the measure across all time. Consider a measure for account balances or product inventory. If we wish to calculate the current product inventory for the year we must return the inventory on the last day of the year instead of summing the account balances for each day in the year.

There are a couple useful DAX functions we can use to find the opening period balance/inventory or the closing period balance/inventory: FIRSTNONBLANK and LASTNONBLANK.

Here is an example of an opening period type calculation. This calculation will return the first non-blank value in a given date range. Ideally, this will be the product inventory on the first day of the month or year.

Starting Inventory:=CALCULATE([Sum of Inventory], 
FIRSTNONBLANK('Date'[Datekey],[Sum of Inventory]))

 

And here’s a closing period type calculation. This calculation will return the last non-blank value in a given date range.
Closing Inventory:=CALCULATE([Sum of Inventory], 
LASTNONBLANK('Date'[Datekey],[Sum of Inventory]))

 

And here’s a sample of the query results:

image

Now I can easily calculate inventory growth rates!

Inventory Movement:=[Closing Inventory]-[Starting Inventory]

 

Inventory Movement Percentage:=DIVIDE([Inventory Movement],[Starting Inventory])

Pretty cool stuff!

image

3. DAX Previous Year, Previous Month, Previous Quarter Calculations

There’s a series of specific DAX functions that I think you’ll find useful for calculating the measures for a previous period:

PREVIOUSYEAR

PREVIOUSQUARTER

PREVIOUSMONTH

In part 1 we looked briefly how to calculate the previous MTD calculation, but these functions can also just be used to calculate the previous period metrics and compare the previous period to the current period.

Here’s a couple of examples of using PREVIOUSMONTH and PREVIOUSYEAR to calculate the Starting Inventory for the previous periods:

Previous Month Starting Inventory:=CALCULATE([Starting Inventory],PREVIOUSMONTH('Date'[Date]))

 

Previous Year Starting Inventory:=CALCULATE([Starting Inventory],PREVIOUSYEAR('Date'[Date]))

 

In the query results we can now see that we’re able to calculate the previous month and previous year starting inventory amounts. Excellent!

image

2. DAX First Day of Year, Quarter or Month Inventory/Balance Calculations

The previous examples in this post have all been calculations relative to the level of the Calendar Hierarchy being browsed, but sometimes we just need to calculate the measure on the first day of the year, quarter or month no matter what level we’re browsing. For those situations, we can use the following functions:

STARTOFYEAR

STARTOFQUARTER

STARTOFMONTH

Here you can see the calculations for the Year Starting Inventory and Quarter Starting Inventory:

Year Starting Inventory:=CALCULATE([Starting Inventory],STARTOFYEAR('Date'[Date]))

 

Quarter Starting Inventory:=CALCULATE([Starting Inventory],STARTOFQUARTER('Date'[Date]))

 

And now we have the Inventory Amounts for the first day of the year or quarter:

image

1. DAX Rolling Sum and Average Calculations

This type of calculation is easier to set in a multidimensional cube in my opinion but with that said I am more of a cube guy. To get this working, I first created a calculation that sums the total Sales Amount for each day in the past 90 days.

To do this I used the following functions:

DATESBETWEEN

FIRSTDATE

LASTDATE

DATEADD

And here’s the calculation:

Rolling 90 Day Total Sales Amount:
    = CALCULATE([Total Sales Amount], 
        DATESBETWEEN('Date'[Datekey],
            FIRSTDATE( /* FIRSTDATE gets the first date in this date range which will be 90 days ago */
                DATEADD( /* DATEADD lets me navigate back in time */
                    LASTDATE('Date'[Datekey]) /* This returns the last day no matter if I'm at the day, month or year level */
                    ,-89,Day
                )
            ),
            LASTDATE('Date'[Datekey]) /* Use the max date for the end date */
            )
        )

 

Review the comments in the code above to get a better idea of what all the functions are being used for.

Then I created a measure that counts the days that exist within the past 90 days. If the day I’m counting is the first day in my calendar that I have data, I only want to count that one day. This calculation is very similar to the previous calculation except I’m using the COUNTX function to count the rows returned.

Day Count:
    = COUNTX(
        DATESBETWEEN('Date'[Datekey],
            FIRSTDATE(
                DATEADD(
                    LASTDATE('Date'[Datekey])
                    ,-89,Day
                )
            ),
            LASTDATE('Date'[Datekey])
        )
    ,[Total Sales Amount])

 

Now that I have a calculation to get the rolling total sales amount and count the days all I need to do is simply divide the two numbers:

Rolling 90 Day Average Sales Amount:
    =DIVIDE([Rolling 90 Day Total Sales Amount],[Day Count])

 

And here’s the query results:

image

Notice that for January the Day Count measure shows only 31. This is because prior to January there’s not data so the Rolling Average is only considering days where data exists. Also notice that the numbers for March and Q1 match since both periods should have the same amount of sales and the same number of days.

Resources

10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

Watch this Power Pivot 101 webinar recording

Feedback?

Let me know your thoughts on these calculations! There’s 100 ways to skin a cat so if you have a different or better way to perform some of these calculations I’d love to see your solution! Thanks for reading! 🙂

Drill Down with Power BI Visualizations

A couple weeks ago in a recent update to Power BI, an enhancement was added to enable a drill-down action in Power BI. This has been something that customers have been clamoring for but now its finally here!

Not all visualizations can be used to invoke the drill-down action. Here are the visualizations that you can use the drill-down action in Power BI: Continue reading Drill Down with Power BI Visualizations

Power BI Tips, Tricks & Best Practices Webinar Recording & Materials Now Available

image

Thank you to everyone that attended my Power BI webinar last month, September 29th. Sorry its taken me a while to finally make the information available, but my schedule has been crazy lately! The good news is, however, the recording is available! So if you weren’t able to watch the webinar live, you can still catch the recording anytime you like.

View the Power BI webinar recording

Continue reading Power BI Tips, Tricks & Best Practices Webinar Recording & Materials Now Available

Creating Time Calculations in Power BI

One of the current issues in Power BI is the inability to specify a Date table. The Date table is what enables us to create some of the powerful DAX time calculations like Year To Date, Month To Date and more when the Date key is not a Date data type. Ginger Grant has blogged about this issue with a proposed work around, which you can read about here. Even though we can’t exactly specify which table is our Date table in Power BI, that doesn’t mean we can’t create some nifty time calculations with Continue reading Creating Time Calculations in Power BI

Join Me for Free #PowerPivot Training w/ @ExcelBIPASS Sept 10 – 12p CDT

image

I’m excited to be able to say that coming up next week on Thursday September 10, I’ll be presenting my session Power Pivot 101: An Introduction to the PASS Excel BI Virtual Chapter! For a lot of users, Power Pivot is like the Ferrari you had in your garage but weren’t aware and that’s one of the reasons I’m so excited to be able to present on this topic. This session is completely free and available to all who would like to attend. It doesn’t get much better than that!

Register for free Power Pivot training

Power Pivot is a powerful yet flexible analytics tool built into a familiar environment yet many users remain unsure of how to take advantage of this dynamic tool. In this session, we’ll discuss the purpose of Power Pivot, where Power Pivot fits within your organization and the basics of designing a Power Pivot model that integrates disparate data sources with the goal of gaining previously unrecognized insight into key business metrics.

This free online training event is scheduled for Thursday September 10th at 12 pm CST/1 pm EST. If you’re interested in attending, all you need to do is RSVP here to let the organizers know you’re coming. It’s going to be a great event, a lot of fun and maybe even educational! 😉

5 Tips for #PowerBI

After a couple months of fun with Power BI, I’ve picked up a few little tricks along the way that have helped me to be able to create some pretty cool data visualizations and dashboard reports. Here are five Power BI tips and tricks that you may find useful as you begin creating dashboards for your organization.

New to Power BI? Start here to get acquainted!

1) Use a pie chart or donut chart as a KPI

One of the ways we can create a KPI visualization is to use a pie or donut chart visualization, which you can see here.

image

In the chart above, I create a KPI to quickly display which tight ends score more than, less than or equal to the average number of touchdowns all tight ends scored last year allowing me to quickly identify tight ends that score more TDs than average.

Here is my calculated column to create the KPI value for you to have as an example:

TD KPI = if(int('TE Stats'[TD])>INT('TE Stats'[Avg TE TDs]),"1",IF(int('TE Stats'[TD])<int('TE Stats'[Avg TE TDs]),"-1","0"))

 

Then configure a pie chart as follows.

First, I place my KPI calculated column as the Legend and as the Values.

Power BI KPI pie chart

Then I hid all the labels and configured the colors to display red (-1), yellow(0) or green(1) depending on the value of the KPI.

Power BI KPI pie chart

Now when I use a slicer to select a player, my pie chart acts as a stoplight KPI. Cool!

Power BI KPI

2) Use a chart as a slicer

I’ve previously blogged this tip before, but this one is too nifty to not share again, in my humble opinion. One of the advantages to using a visualization like a funnel chart as a slicer is you gain the ability to single-select a filter, which is something the current slicer lacks. Check out this post to learn more about leveraging Power BI’s natural cross filtering to create some pretty cool slicers.

image

3) Create a spark line with a line chart

A nice way to create a small trend line, also called a spark line, is to use a line chart visualization which you can see below.

Power BI sparkline line chart

This trick is pretty easy. Just create a normal line chart visualization, hide all the labels and shrink the chart down to the desired size.

4) Use a scatter graph and matrix to create a calendar chart with day labels

Last week you may have seen my blog post on how to use a scatter graph to create a calendar chart. One of the ways you can improve the calendar scatter graph is to create the visualization along side a matrix visualization, as seen below.

image

You can use the matrix to display the names of the week below each day in the calendar and then also optionally display the totals by day.

5) Right align the y-axis on a bar chart to prevent the labels from hiding

The bar chart is a great visualization type to use in your Power BI dashboard because its so easy to differentiate the differences between the categories. But one of the problems with the visualization in Power BI is that sometimes its hard to see the categories on the y-axis if the chart is too small. See the image below to see an example the issue I’m talking about.

image

One way you can work around this is to right-align the y-axis. This will cause the full value of the y-axis categories to always be displayed in all their glory albeit on the right side.

image

You just have to live with the category labels on the right side of the bar chart.

Resources

Need more Power BI tips? Check out these tips:

Here’s three Power BI best practices to follow.

Here are the new visualization types in Power BI.

Converting Power Pivot models to Power BI is now a thing!

Taking #PowerPivot to the Next Level

Power Pivot is an amazing, flexible and powerful business intelligence tool (among other things) and there is no doubt about that fact. As a feature included with Excel 2013 and 2016 (and an add-on for Excel 2010), Power Pivot allows user with a little technical expertise to integrate disparate data source together within a flexible data model. Once the data is loaded into Power Pivot, we easily have the ability to create powerful calculated measures, key performance indicators Continue reading Taking #PowerPivot to the Next Level

Importing Power Pivot & Power View into Power BI

Import Power Pivot into Power BI

The Power BI August update just rolled out today (8/20/2015) and in the latest update there’s a lot of cool, new enhancements such as writing custom MDX or DAX queries to access your SSAS data sources, connectors for Azure HDInsight Spark and Azure SQL Data Warehouse (so awesome!), some various UI improvements and a bunch more. But one of the coolest features (and much needed IMHO) is that we now have the ability to import Excel artifacts, such as Power Pivot models and Power View reports straight into Power BI Desktop!

New to Power BI Desktop? Read this first!

Import your Power Pivot Model into Power BI

To begin importing a Continue reading Importing Power Pivot & Power View into Power BI

Power BI Tip: Use a Scatter Chart to Create a Calendar Report

Power BI Desktop Scatter Chart

The Scatter Chart in Power BI and Excel is very useful chart for visualizing three different metrics in tandem. But with a little bit of work you can use a Scatter Chart to create a Calendar chart for visualizing your metrics across the days of an individual month.

New to Power BI Desktop? Start here!

To configure a Scatter Chart too mimic a Calendar type report, you need the following: Continue reading Power BI Tip: Use a Scatter Chart to Create a Calendar Report