|Get Data from File|
|File Not Supported|
|Quick Insights for Sample|
There's one more cool thing you can do with Power BI Online. You can ask natural language questions to your data using the Q&A feature.
|What are my total sales?|
|What are my total sales for 2012?|
|What are my total sales of Tables for 2012?|
Interestingly enough, Power BI Desktop is capable of connecting to the original Excel file, even though it's not a recent version. Moreover, it can also connect directly to the tabs instead of us having to assign tables. It also attempts to automatically detect relationships so that we don't have to explicitly define them. As a note, automatic relationship detection may not work if you have unorthodox relationships or your columns are not named the same. We'll talk more about modelling in a later post. As you can see, we have modeled the data appropriately.
|Sales by Order Date|
|Sales by Order Date (Line)|
We can easily do that by clicking the "Line" button in the "Visualizations" panel. Now, let's split these lines by Category.
|Sales by Order Date and Category|
We can do that by simply dragging Category onto the chart. What if we want to look at these at the Month-Year Level? It turns out that this is one of the places where Power BI falters. Not all charts have all capabilities. For instance, there doesn't seem to be a way to drill down a line chart. You can only show one dimension at a time. We even decided to create a new column in our data model called Order Month and dragged it onto the chart. We could not have Year and Month on the chart at the same time. Therefore, if you want to see Month Year, you have to create a single calculated column that combines the two or better yet, have a date dimension that does it for you. Fortunately, Matt Masson blogged about a way to do this using Power Query. You can find the post here. Now, we've run into the problem of using string names (which are sorted alphabetically, but this can be changed) or using YearMonth numerics, such as 200901 (which have huge gaps between 200912 and 201001). Either way, this is disappointing. Fortunately, bar charts give the option to drill-down. So, we have something we can use, even if it's not perfect.
|Sales by Year|
|Sales by Quarter|
A caveat of this approach is that you can't show multiple quarters across multiple years at the same time without using more advanced hierarchies. You can either drill down into a single year (by clicking on the bar) or drill down across all years (by clicking the "Double Down-Arrow" button in the top-left). Hopefully, someone in the community has come up with a good workaround for this. Alas, we have seen some very powerful options for a free tool. Stay tuned for more posts about the new Power BI. Thanks for reading. We hope you found this informative.