I am happy to announce that my first detailed training course is now up and running at Udemy. It is about Power BI. But it starts with what you already know – Pivot Tables. That is why the course is called Pivot Table to Power BI.
Tag Archives: Power BI
DAX RelatedTable and behind the scene PowerPoint animation
Here is an interesting way to learn two things in one go. While creating the Power BI course for UDEMY, I created lot of explanatory videos. One of the DAX functions which is difficult to understand is the RELATEDTABLE function. So here is a dual video which explains DAX RelatedTable animation. 8 minutes.
Power BI Lat Long Converter
Power BI and Power Map support mapping your own data using Latitude and Longitudes. Lat-longs are available in various formats. Power BI supports only decimal format. Unfortunately lot of data still comes with the DMS format Lat-Longs. I created a simple tool: Power BI Lat Long Converter. Use it do perform bulk conversions quickly. Continue reading Power BI Lat Long Converter
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.
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.
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.
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 …
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
| 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.
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 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.
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 nitin@maxoffice.biz
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
- Create the data model as required.
- Now go to the Excel workbook and choose Insert – Pivot Table.
- Please note that there is no data in the Excel sheet.
- Now, from the dialog, choose the checkbox Choose this workbook’s data model
- A regular pivot table is now created.
- All the expected features are now available.
- That’s It. Enjoy.
