Category Archives: Power BI

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.


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 =
    SUM ( Query1[Likes] ),
    CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () ),

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.


Convert Power View dashboards to Power BI Desktop

Although Power View and Power BI reports are similar, Microsoft is focusing on the later. Power BI platform is being separated out from Excel for many reasons. One of the reasons is to eliminate the dependence upon Silverlight (technology used to create Power View, which is now being phased out).

Here is a 2 minute video which explains how to convert Excel based Power BI components like data mode, queries, relationships and Power View dashboards into Power BI Desktop. Once converted, you can publish these to the Power BI portal.

Don’t use Power BI with PowerPoint

I work with many customers on Power BI. It is a new topic for many users and decision makers (not techies). The benefits of using Power BI are obvious and everyone wants to use it immediately. However, the first question I get is – How to use Power BI with PowerPoint ?Here is the answer. Don’t try to put Power BI dashboards or visualizations into PowerPoint. Simple. If you want to know why, read on …

Why not use Power BI with PowerPoint

Continue reading Don’t use Power BI with PowerPoint

Power BI Glossary in plain English

Power BI Glossary is a one line description of relevant terms. Jargon free and easy to assimilate. This is actually part of the Power BI Udemy course I am working on. Let me know your feedback.

Power BI Glossary poster

Power BI Glossary

Data Information
Raw data Data as it is received by you
Good data Information in a table form
Bad data Data which is not good
Table Good data with a name
Summarization Roll up or combine raw data into smaller, easier to grasp information
Analysis Finding all possible useful things from the data
Pivot Table Popular tool to summarize tables and analyse the information
Report A way of sharing results of analysis
Action Using the analysis to eliminate bad things or increase good things in future
Power BI Set of new tools which allow even more, faster and easier analysis
Power BI Desktop Windows application which helps in gathering data, cleaning and analysis
Visualization Showing data using a graphic rather than just numbers and text
Dashboard Multiple, related visualizations shown together
Interactive Dashboard Dashboard where changes to one item affects the related visualizations
Power BI Portal The place on internet where you can publish and share the analysis
Power BI app A mobile app for viewing and interacting with Dashboards

Where is your data stored in Power BI portal

This is a common concern amongst customers. Many countries have a requirement that their data should be physically stored within a data center located in their country. Here is a simple way to find out where exactly your data is stored in Power BI.

The steps

Login to Power BI Portal and click on the Help (Question Mark) button. Choose About Power BI option.

About Power BI

Where is Power BI data stored?

Now it shows a dialog which displays the actual country in which your data is stored. Of course, if you want to change that location, you will need to talk to your Microsoft account manager or partner.

Power BI portal

Power BI Desktop course – Need your inputs

Power BI is powerful, as the name suggests. But it is also confusing. Exactly what what does Power BI mean? There is no simple, single line answer. Let me try. It is a set of new tools to analyze old data in new ways. Today the simplest way to get started it to sign up for a free Power BI subscription and use Power BI Desktop tool to create your reports and dashboards. I have already written an introductory article and published a walkthrough video.

Power BI Desktop course on Udemy

I feel this is one tool which can transform the way we work on data and analyze it. The primary reason is that it is independent of Excel. So all the bad habits which we have developed in Excel over decades simply disappear! I am planning to create a course on Udemy for Power BI Desktop. I would like your inputs on the content and any additional ideas or wish list. Read on to have a look and post your comments. You can also send a mail to me on

Continue reading Power BI Desktop course – Need your inputs

How to create a regular Pivot Table from Power Pivot Data Model

Power Pivot Data Model adds database like features to Excel. Power Pivot allows you to handle millions of rows of data, from multiple data sources and lightening fast analysis. It also eliminates the need for using VLOOKUP function for decoding codes into descriptions.

Power Pivot does NOT provide Grouping, Calculated Columns and Calculated Fields features. These need to be managed using related tables and DAX formulas. This can be a limitation in some cases – especially when you are new to Power Pivot.

In some cases, you want best of both worlds. The database consolidation, relationships and performance of data model. But at the same time, you also want the features of Pivot Tables which you are used to for so many years.

The good news is that you can do it quite easily. Here is how (requires Excel 2010 & higher).

Create Pivot Table from Power Pivot Data Model

  1. Create the data model as required.
  2. Now go to the Excel workbook and choose Insert – Pivot Table.
  3. Please note that there is no data in the Excel sheet.
  4. Now, from the dialog, choose the checkbox Choose this workbook’s data model
  5. A regular pivot table is now created.
  6. All the expected features are now available.
  7. That’s It. Enjoy.

Create Pivot Table from Power Pivot Data Model

When to use Remove Top and Bottom Rows options in Power Query

Here is a quick post to show the practical scenario when the Remove Top and Bottom Rows options are useful while cleaning up data using Power Query.

Remove Top and Bottom Rows option in Power Query

Remove Top Rows

Often we get CSV files which have tabular data. Sometimes, there is a title, blank rows and some other related but unwanted data just above the useful tabular data. In such cases, we use the Remove Top Rows option.

Remove Bottom Rows

If you get data from web pages (HTLM tables), the header row is repeated at the bottom. This option helps you remove the unwanted row.

Another common issue is that many reports dump data with a grand total at the bottom. Remove Bottom Rows option is also usefu in these cases.

Best Practice

It is quite possible that we focus on the top few rows while cleaning up data. It is equally important to import the data and look at the bottom rows. The bottom part of the data can have many issues which often go unnoticed and may even generate inaccurate results.