How to Eliminate VLOOKUP using Relationships in Power BI

VLOOKUP is one of the most commonly used functions in Excel.  However, it comes with its own set of problems:

  • It increases the file size
  • It consumes lots of processing power, making things slower
  • It is limited to a single table.  This means you cannot use VLOOKUP to search for a value in a different table or sheet
  • It only looks right
  • It can only handle a single criteria
  • If the lookup column contains duplicate values, it will match the first value only

With Microsoft’s Power BI, we now have a simpler and better alternative to handling VLOOKUP limitations using Relationships.  Watch this video to learn more:

 

Power BI Udemy Course coming very soon

This video is one of 30 lectures in my soon-to-be-released Power BI course on Udemy. It is my first ever course in the e-learning space. I am currently working on the last lecture, and the course  will be released in a matter of days. Watch this place for further announcements.

Quick Calc in Power BI

Quick Calc in Power BI (Desktop as well as Portal) is a handy feature. Similar to Show Values As in Pivot Tables.

How to use Quick Calc in Power BI

When you add a measure in the value area, it can be shown as % of Grand Total. Just open the dropdown next to the field and choose Quick Calc.

Quick Calc in Power BI - menu option  Quick Calc in Power BI - Dialog

In the dialog, choose the summarization as desired and then choose Percent of of grand total option from the Show Value As dropdown.  Here is the result. I have added the same column twice. Once to show the actual and other as % of total.

image

With little bit of tweaking of formatting options in Power BI designer, nice looking reports can be created. One of the most common complaints is that the default font size is too small. That can be changed by going to the General section of formatting area.

Font options in Power Bi

The Show Values As dropdown has only one option as of now. Of course, it is nothing compared to the Pivot Table Show Values As capability – which offers  14 options. I am sure more options will be added to Power BI soon.

Eliminates a measure

Without Quick Calc in Power BI, you would have to create a measure for the % Total with the following syntax.

%ofTotal =
SUM ( Query1[Likes] ) / CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () )

Or better still, to avoid DIV/zero error in a single command:

%ofTotalBetter =
DIVIDE (
    SUM ( Query1[Likes] ),
    CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () ),
    0
)

The base formatting in Power BI itself is rather primitive at this point of time. This code was formatted using the amazing tool created by Alberto Ferrari and Marko Russo called Dax Formatter. You just copy paste valid DAX code there and it formats it automatically. Output can be an image, HTML or Word. API also available.

Power BI Udemy Course coming very soon

Power BI is becoming more and more powerful day by day. It is time everyone got to know about it. It is the next natural progression from Pivot tables. It is not just easy to use. It gives you more capabilities which you have never had before – and that too with lesser effort.

As promised, the detailed Power BI course is almost ready. It will be released in the near future. Watch this place for further announcements.

 

Office 365 Worst Practices – Part 10 – No Focus on Office

Here is the next installment in the Office 365 Worst Practices series.  Office 365 contains many components. For regular users most of these components are new, except one – Office. Training programs are conducted during the adoption phase to educate users. Unfortunately, the most important component of Office 365 is usually not covered at all during the adoption phase – that is Office itself.

In this post I will explain why this is a major problem and how to solve it. In summary, if you do not know how to use Office well, you will never be able to get maximum gains from Office 365 platform.

Office 365 Worst Practices

Continue reading Office 365 Worst Practices – Part 10 – No Focus on Office

How to conduct simultaneous Remote and Local Training

Traditionally, training is either performed for remote audience or local audience. However, in recent times, I am getting frequent requests to deliver remote and local training simultaneously.

I recently conducted a large training program where we covered 2000+ people in just one day. Only 300 people present in the room, rest on Skype for Business, across 6 countries! I learnt many best practices during this session. Here are the best practices.

Remote and Local Training

Continue reading How to conduct simultaneous Remote and Local Training

Announcement: The Amazing Mouse Ebook (and 50% discount code) is here

The Amazing Mouse eBook

Learn 300+ powerful ways to use the mouse efficiently with
MS Office. Save at least 10 minutes & reduce the risk of arthritis!

Buy this Mouse eBook for USD 9.95

Introductory Offer

Buy NOW and get 50% off till July 31st, 2016
Discount Code:  JULYPROMO

Discount Code:

Mouse eBook - Money back guarantee

Continue reading Announcement: The Amazing Mouse Ebook (and 50% discount code) is here

Achieve more with less effort by using Office 365 platform efficiently. Learn from the Efficiency Guru, Dr. Nitin Paranjape @drnitinp