Power BI Creates Left Joins By Default

Microsoft Power BI is a great tool to visualize data quickly and create management dashboards.
We can load data from almost any data source, and create relationships between different tables, just like in a traditional RDBMS (Relational Database).

Join appear as Equal Joins in Power BI.

This means that when we join 2 or more tables, with one table being the dimension table, and the other the Fact table, then the dimension filters the fact table records that match the criteria (which can come from a Dimension Table, Page filter, Report filter, or through the interactions between the different visualizations)

So a Matrix report or a Chart in Power BI will only show values from the Transaction table where the row from the dimension matches.

  • If we want to see all categories from the Product table, we can just select a category.
  • This will display all distinct categories, irrespective of whether there were any sales for these product categories or not.
  • However, as soon as we bring in the Sales Amount from the Sales Fact table, it filters down the sales amount for each category and then summarizes it.
  • This means that only the categories where there were sales will be displayed.
  • And the categories where there are no sales will be ignored and hidden.

But by tweaking the Category to display Values With No Data, we can see all categories, with or without corresponding sales.

See the step by step video to learn how to enable the Show Values With No Data option in Power BI.

I hope you like the tip. Do like it, and subscribe to the ExcelChamp’s YouTube Channel for more PowerBI Tips like this.

Thanks,
Vinai

Viewing the Formula in Another Cell in Excel

Applicable For: This tip works in Microsoft Excel 2013 & Excel 2016, and Office Excel 365 (for both Windows & Mac Editions)

Prior to the Excel 2013 edition, to view all the formulas in a given worksheet in Excel, we had to use the View Formulas button, or use the nifty shortcut I highlighted in another post on this topic – View All Formulas in Excel with a Single Click.

But from the Excel 2013 and onward editions (including Excel 2016 – for Windows & for Mac), we have another better way to view the formulas used in calculations. This method is fantastic, because it allows you to see the formulas, without having to flip the switch, and see only values or only formulas.

In this technique, the original values and formulas can stay where they are. We can simply use the newly introduced Excel function in a new cell, which will then show you the formula of any given cell pretty easily.

This new function is called FORMULATEXT.

=FORMULATEXT(cell_reference to a cell containing a formula)

FormulaText Demo from ExcelChamp.net - By Vinai Prakash

With this new function, you can see the formula within any other Excel cell, without flipping up the on/off option. It allows you to see the value and see the formula, all at the same time. Much better than the chicken only or egg only options…

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


This has been specially great while teaching or showing off stuff to someone. Now you can use complex formulas, and the FormulaText function will show the formula, while the original value stays put, making it easier to understand the formula and its working, while having the value displayed directly.

Great, simple tip. I hope you like it!

Cheers,
Vinai Prakash,
Founder: ExcelChamp.Net – Effective Tips to Simplify Excel, Every Day!

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

6 New Chart Types in Excel 2016

Before the year 2016 begins, Microsoft has already unveiled Microsoft Office 2016 suite – with a number of enhancements, features, and completely new things that extend the existing Excel and takes it to new levels.

In the latest and greatest Microsoft Excel 2016, we see 6 new types of charts, which will help to transform the data into much better insights, information and visualization delight than ever before.

Microsoft Excel 2016 boasts New Charts that can display:

  1. Sunburst Chart
  2. Treemap Chart
  3. Waterfall Chart
  4. Stock Chart
  5. Pareto Chart
  6. Box and Whisker Chart

shows the new charts in PowerPoint2016

The Sunburst chart looks like a pie chart, but has rich, extended functionality. You can now visualize the data at multiple levels, which was simply not possible with a pie chart.

Business Analytics in Excel 6

The Waterfall chart in Excel is a welcome addition. Previously, we had to write cumbersome VBA code, and even use external charting applications to create waterfall charts. This type of waterfall chart is great to show stock price movements.

Business Analytics in Excel 7

A Pareto chart shows the 80-20 Rule, which applies to any business, in any industry, and has been proven to be a great indicator of the top KPIs that make the difference. Doing a 80-20 Pareto Analysis required us to Build a 2 Axis chart in previous version of Excel (like Excel 2013, Excel 2010 or Excel 2007 etc.)

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Excel 2016 Software: If you do not have the Microsoft Excel 2016 software yet, you can easily get it online here.

Want to Learn Excel 2016: There are several books on Excel 2016 already available, and you can also join the ExcelChamp’s Online Training for the Pivot Table MasterClass, available on TruEducate website. A few, short videos will teach you the master techniques that are used to play with Pivot Tables, and generate powerful reports from Excel Data using Pivot tables. This video training is recorded and provided directly by me, Vinai Prakash, at the TruEducate Website.

These new chart types in Excel 2016  will help us in creating beautiful charts in Excel, and take it to the next level of visualization of data, and presentation for our clients, management, users, and for our own data analysis and charting analysis.

In the coming weeks, I will be highlighting more new features of Microsoft Excel 2016. Do let me know if I can help you in any way in using Microsoft Excel 2016.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash
Founder: ExcelChamp.Net – Simple Tips to Get More out of every day Excel, and be an ExcelChamp!