Posts

Zebra BI Power Platform Tour Copenhagen Power BI sales financial dashboards Andrej Lapajne

Zebra BI attends Power Platform World Tour Copenhagen, 11-12 September

We are happy to inform you that Zebra BI will attend yet another #PowerPlatformWT event, this time we will be in Copenhagen.


Zebra BI  and our CEO Andrej Lapajne (who will host a session TOP 5 DAX tricks for sales&financial dashboards in Power BI) are headed to Power Platform World Tour stop in Copenhagen, to attend and meet both Microsoft and industry experts, explore the latest business challenges and solutions across various industries and see the innovative world of technology in action.

If you are in Copenhagen or nearby make sure you  REGISTER HERE - there are still some open spots waiting for the ones who would like to get unparalleled access to premium Power Platform content and industry leaders.

About Zebra BI and the session: 

A good DAX model can significantly shorten and simplify the development and maintenance of your Power BI dashboards. More importantly, smart DAX tricks will enable you to build super effective and user-friendly dashboards, where the end-user can simply switch the whole report page from monthly to YTD view, dynamically display different measures in the same visual, present meaningful actual vs budget charts in a few clicks, etc.

You will master top 5 practical DAX tricks that have been applied with great success in numerous real-world Power BI projects. [Extra BONUS] We will share the DAX code and PBIX examples with all participants! 🔥

Surely some good enough reasons that you book your attendance for Andrej's talk which is due on Thursday, September 12, 2019, 14:15 - 15:15 local time.

 

Very much looking forward to seeing all of you there and getting to know you.

Event location:
Amager Blvd. 70,
2300 København,
Denmark

Dynamics 365 Power BI Custom Visuals Zebra BI Microsoft

Dynamics 365 meets Power BI with Zebra BI – Antwerpen 19.06.2019

We proudly announce that Zebra BI was invited to a special kind of breakfast - the one where Dynamics 365 meets Power BI.


Zebra BI  and our CEO Andrej Lapajne are headed to Antwerpen to attend a special seminar called Dynamics 365 meets Power BI. The topic they will focus on is going to be: how to quickly analyze data from Dynamics 365 with the help of Power BI and custom visuals. Another speaker at the event will be CRM strategy & implementation expert Jure Jesenovec, the Managing Partner at Admiral Dynamics.

If you are in Antwerpen or nearby make sure you  REGISTER HERE - there are still some open spots waiting for the ones who would like to get an overview and an explanation of the possibilities with the use of Power BI and custom visuals within Microsoft Dynamics CRM.

Event details: 

Dynamics CRM system is the hub for all your customer processes and data. If you enrich this information with insights from Power BI, you can create an "all-in-one solution" for steering and managing your business processes.

Dynamics 365 covers the processes, data, user interfaces and management aspects, but what about data visualisation? Power BI enables you to make data more useful and provide greater insights. Take your organisation to the next level with Power BI.

This breakfast session on Wednesday 19 June introduces participants to the use of Power BI and Zebra BI custom visuals with Microsoft Dynamics CRM.

The course focuses on the use of Power BI functionality and technical possibilities of analysing data trends within their CRM organisation. Secondly, we’ll demonstrate the rich and clear view of your extended sales data that provide comprehensive insights for management.

Agenda at a glance:

08:30 - 09:00 

  • Welcome & Breakfast

09:00 - 11:00 

  • Dynamics 365 Meets Power BI: How To Quickly Analyse Data From Dynamics 365

 

Very much looking forward to seeing all of you there and getting to know you.

Event location:
Katwilgweg 2,
2050 Antwerpen,
Belgium

Supercharge Your Power BI Dashboards with Small Multiples [Webinar]

Small multiples are one of the most powerful data visualization methods for financial reports and dashboards.

In this webinar Andrej Lapajne will show how you can leverage them to make your sales, marketing, and financial dashboards in Power BI stand out!

Watch webinar recording and
download PBIX examples

Small multiples defined

It doesn't matter what field you work in - you probably needed to compare multiple categories of data at some point.

You might be working with cost centers, accounts, sales locations, sales channels, regions, car models, web traffic referrers, etc. And when you work with multiple categories of data, you don't need complex or cumbersome approaches with multiple pages or tables that are hard to read. There is a way to display it in a way that instantly makes it much more insightful and easier to compare sets of data on a single page.

The visualization is called small multiples and at its core, it's a simple concept. In essence, you

  1. put all your charts displaying different categories on a single page, and
  2. scale them (synchronize the Y-axis).

Using small multiples allows anyone looking at your dashboard to immediately see which categories are the most important and also analyze the trends.

Examples of small multiples in Power BI

Sales in 8 markets

Take a look at the this simple example:Small multiples - Repeat charts and scale themFirst, there are eight charts representing sales in eight countries, which is the defining feature of small multiples. The second mandatory feature is that they are rendered using the same scale, meaning the Y-axis needs to have the same maximum across all the charts.

Just a glance is enough to see that the US is the biggest market with planned 25 % growth. Germany, on the other hand, has lower growth potential despite being a similarly large market. France has been declining for the past 3 years and this is expected to continue.

On-time delivery of products

Let's take a look at another example to really show you the power of small multiples. This one displays on-time delivery performance in relation to the goal:

Small multiples - On-time delivery vs GoalThe company's goal is to have 80% of its products delivered on time across all categories. The goal is visualized with the dashed line.

Once you have information aggregated on a single screen, you can start working on making it more meaningful. For example, using a combination of a line and area chart - called the Hills and Valleys Chart - emphasizes the positive and negative variance in relation to the goal.

Small multiples - On-time delivery with Hills and valleys charts

The red and green variances make it much easier to spot what's going on. We can see that the Cell phones category had serious issues that were resolved, while Video games are on an improvement trend.

Retail sales by category

Another great example of using small multiples is sales. Below is a sales dashboard for a retail company:

Small multiples - Retail sales by category with waterfall chartsOne look is all it takes to see that women's fashion segment is in serious trouble.

Multiple KPIs

Until now we dealt with small multiples that visualized a single KPI, like market share, sales category and similar. This is the simplest type of a small multiples. The data is simply repeated over and over for all categories.

The next example is a bit more complex. We are visualizing multiple KPIs of the profit & loss statement:

Small multiples - multiple KPIs (profit & loss statement)This is dashboard is harder to build because KPIs can have different meanings. For example, while revenue growth is normally marked in green, any growth in costs should be marked in red because that's a negative trend in a KPI.

When creating a small multiples dashboard like this, you need to be pay attention to the type of the KPI and know when its meaning should be reversed.

You can see more examples of Power BI dashboards in our gallery: https://zebrabi.com/pbi/gallery/.

Small multiples in Tableau vs. Zebra BI

Creating a small multiples dashboard well is hard. What makes it easier is using the right tool for the job.

Tableau software is a tool known for doing small multiples, but the dashboards it produces are not optimized. Here's an example:

Small multiples in Tableau software (layout is not optimal)If you look closely at the example, you'll notice that all charts are placed on a grid with equally sized cells. This results in lots of wasted blank space.

Now let's look at what Zebra BI offers in Power BI with its Smart Rows and Auto features. The grid here is optimized based on the space individual charts actually need:

Small multiples in Zebra BI (optimal layout with Smart Rows)The next level is to display larger charts across two rows. This is what we call the span function, which means that charts that are big enough simply take more rows in a column. This gives you true flexibility when working with small multiples. The charts are given the space they need and correct scaling is preserved.

Small multiples in Zebra BI (optimal layout with Span Function)Website conversions

Now we can look at an example dashboard built on data from Google Analytics. It displays different sources of traffic to the website and you can quickly see that organic traffic plays an important role as do email campaigns.

Small multiples - Google Analytics (goal completions by traffic source)However, the number of different traffic sources is very large and a large majority do not really contribute much individually. Therefore it makes no sense to display them on the dashboard, which is where the Top n + others feature comes in.

Instead of displaying hundreds of charts, the Top n + others feature displays a specific number of top categories and sums up other in a category called Others. This means that everything beyond this point is summed up in this automatically calculated data category.

Create your own small multiples dashboard in Power BI

We will now show you how to start creating small multiples using Zebra BI for Power BI.

Create small multiples
from your own data

Load up Power BI, and use Zebra BI Power Charts Visual in the provided example files. On a blank page, add sales actuals and compare them to the previous year split by quarter. You do this by selecting AC and PY checkboxes under Sales field group and Quarter field under the Calendar group.

Now add the business unit dimension to get the small multiples chart. Adding this dimension will display actual results compared to plans by quarters for each individual business unit.

This displays all the charts on the dashboard. Since there are so many, they will become small and that's why this visualization is called a small multiple. You are now dealing with limited space so you need to work on the labels. Make sure everything is not too crowded.

Open the Format tab and select the Data labels group. Set the Units to "Thousands" and set the "Show units in" to "Title" value. This re-formats numbers and makes them more readable. Set Decimal places and Percentage decimals to 0 and set Density to only show the first, last and minimum and maximum values.

Next stop under the Format tab is the Small multiples group. Under the Layout option, you can select Smart Rows, which sizes rows based on their values - larger rows for larger values You also have the Auto layout option that better handles scaling when you have large values. In our case, make sure you switch to Auto to make larger charts larger and optimize the utilization of space.

See the comparison of the Auto mode to the Smart Rows.

Under Sorting, the best option is normally Descending to put the largest values first. You can also turn on the grid if you like to improve readability.

Tip: Don't use strong or dark colors for borders and grid lines.

Working with small multiples

Zoom in

On your small multiples dashboard, you can click on the title of each individual chart to zoom in. Zooming in helps you better understand what's going on with each individual chart.

Break axis

Sometimes you want to dive in and understand the variances a little bit better. Small multiples can be hard to read and you can turn to the Axis break feature (in waterfall charts) to resolve that. By clicking on the AC or PY column in the chart, you can break the axis while the scaling between the charts is preserved.

Just click to enable or disable it.

Axis break allows you to better compare the rates of your variances. If you want to see the variances better, you can set the axis break to a higher value - 80% for example - which results in more pronounced variances.

Select the right layout

Of course, you can switch between different types of charts and each type also has different layouts. Let's take a look at Area charts - here the default value is the layout called the Actual, which means that actual (this year's) values are compared to the previous year.

If you switch it to integrated variance layout (Chart settings - Layout - Integrated variance), the variance between the values is rendered as well. This results in more emphasis on variances. With this toggle, you can easily switch between different layouts to emphasize the things that are important to your data story.

Use dynamic data exploration features

Another powerful feature is the combination of a Zebra BI Power Table and small multiples. This approach allows you to easily explore data using the table to filter and compare small multiples. For example, just select the Product Revenue in the table and enjoy the details on a large chart. But you can also Ctrl + click multiple elements to select and compare them.

For example, you can compare product revenue to service and other revenue. Or take the product revenue to compare it to the product cost. This gives you unrivaled flexibility to explore data.

Watch recording and get PBIX examples

Enter your data below to get immediate access to the full 1-hour video recording of the webinar and all PBIX examples used in the webinar.

  • I agree to getting instant access to the webinar and receiving educational content from Zebra BI.
  • This field is for validation purposes and should be left unchanged.

Privacy Policy

 

Zebra BI visuals for Power BI 3.1 released

We work around the clock to support you in the best way possible on your quest to leverage your data in a whole new way and gain the unique added value which helps accelerate your business.

We are launching the latest release of Zebra BI custom visuals for Power BI - version 3.1. It is packed with new and improved features that will help you create even better reports and dashboards in Power BI.

One of the features most sought-after by our clients and users of our visuals is the "Cross-tables" feature. You can now create cross-tables, time-tables, and P&L tables with multiple business units and across multiple years. Andrej explains all about it in the video below.

Another great new feature is the Matrix chart, an exciting new way to visualize your data and gain additional insight. The new version also brings further refinement to our small multiples concept. Essentially, we put them on steroids and they now allow you to zoom into a single chart, resize it, drag the mouse just once to display the entire matrix of small multiples available, etc.

In short, the new version brings a lot of significant improvements. Please, take your time and watch the brief video presentation of everything we’ve prepared for you.

Enjoy. 😊

 

Here is also the embedded Power BI report, which allows you to click through all the new and improved features of Zebra BI for Power BI 3.1.

Try Zebra BI version 3.1

 
power bi custom visual

Zebra BI Recommends: 10 Power BI Custom Visuals that Will Make Your Reports Pop

It has never been this easy to visualize and tell stories with your data. Tools like Power BI deliver real power that anyone can access and use to create great visuals that explain, clarify and amplify your message. However, if you work in Power BI you might need some help to do that. That's where Power BI custom visuals come in. These add-ins expand your options with additional visualization features that you can use to create compelling visual stories.

To help you start, we're highlighting some of the custom visuals that will make your reports stand out.

1. Card with States

Power BI already has a built-in card element but Card with States by OKViz supercharges it. You take a performance measure and define multiple states that determine the colour of the visual. If the measure is positive, the card is green and if it underperforms, the card turns red. It is also possible to display the measured trend line. Powerful and simple at the same time.

2. Gantt Chart

 

MAQ Software developed an upgrade to regular Gantt charts. It offers features such as the ability to view data related to the task and the hierarchy of data categories and to sort the data based on any data point contained in the task. The visual can be used by project managers to monitor projects as well as by users to view individual tasks and progress. These attractive charts will definitely help you improve your project management efforts.

2. Text Filter

Sometimes the most powerful things are the simplest and this custom element fits this description perfectly. All Text Filter does is add a search box to your dashboard that you can use to filter your data. For example, we have a demonstration dashboard that displays information from hundreds of stores and to easily display just a subset of them, you need to simply enter some text, such as "new york" into the search box. The data is filtered in real time and helps you find specific content on your dashboard.

3. PowerApps

Like the previous item on this list, this is another custom visual developed by Microsoft. Since Power BI is a part of its Power platform, it's no wonder Microsoft added support for PowerApps. This custom visual makes it easy to bring forms and data editing to Power BI. You can even connect to external data, stored in services such as SharePoint, Dropbox, and MailChimp. It can be used to easily enter parameters for what-if analyses or to create an inventory dashboard through which you can place orders as needed.

At Zebra BI, we recommend our customers add commenting functionality to Power BI using PowerApps. All you need is to create a simple app that is used to add comments, which are displayed as a tooltip or in a separate table. Currently, this is the only native option to implement live commenting in Power BI.

5. Instagram-like Overview

You could also go Instagram on your data. We don't mean filters but showing your list-oriented data in Instagram-like formatting. You can take data about companies and show their icons, names, hyperlinks and additional data fields and display it as an easy-to-read overview. This is exactly what Overview by CloudScope does.

6. Mapbox Visual for Power BI

If you're working in multiple geographies, you often need a way to put visualizations on a map. Power BI can be extended with customizable heatmaps, point cluster maps, and graduated circle maps that are available in Mapbox Visual for Power BI. This plug-in allows you to overlay Power BI data on a satellite map. It offers several advanced features, including the ability to visualize data differently at different zoom levels through layers. In practice, this means that you receive more detailed data the closer you zoom in on the map.

7. HierarchySlicer

Slicers are a great way to switch between different filters, especially those that you use often. You might want to use more complex slicers and that's where HierarchySlicer comes in. It allows you to use a Power BI hierarchy as a slicer to filter other report items. You can expand individual levels to navigate through the hierarchy and you can use either predefined or ad-hoc hierarchies.

8. Add Natural Language Insights for BI

Arria NLG's add-in for Power BI automatically creates narratives for your data. It analyzes your data to create natural language narratives. You can set various configurations to select the level of detail to share or even use the NLG Studio to program your narratives down to the smallest detail. You can add your specific knowledge for your industry to drive compelling stories or just highlight the most important facts. This is truly one of the more interesting custom visuals out there.

9. Power BI Visual Planning

Power Planner Visual Planning is a powerful tool for budgeting, planning and forecasting. It allows corporate users to see a data visualization as they are entering data. When they change or add data in a dashboard or a report, it changes interactively. This makes it easier to adjust projections and forecasts and observe the impact immediately.

10. Zebra BI visuals

A list of custom visuals for Power BI would not be complete without our own Zebra BI for Power BI. These extremely advanced visuals enable you to create convincing financial reports that make a point and reveal hidden insights. They are the first and only IBCS®-certified visuals on the market and allow you to create complex visual reports that combine waterfall charts, small multiples and other advanced features. Fully responsive and interactive, they allow you to take meaningful decisions based on actionable insights.

 

Don't just take our word for it, go ahead and sign up for a free trial. The solution is so powerful even Microsoft uses it internally for their reports.

 

 

 

 

 

 

Financial Reporting in Power BI with Zebra BI – Zagreb 14.06.2019

We are thrilled to announce, that we'll be running another full-day workshop on financial reporting in Power BI with Zebra BI, taking place in the beautiful city of Zagreb


Controlling Kognosko is where the event will take place, presenting the latest trends and best practices of financial and business reporting. Our CEO Andrej Lapajne will lead the full-day training on financial reporting in Power BI with Zebra BI visuals. With a ton of examples, modeling best practices, DAX tricks, and advanced data visualization methods, we will systematically teach you how to build superb P&L statements, actual vs. budget dashboards and other business reports in Power BI. From scratch! We will show you how to use PowerBI's and ZebraBI's bells & whistles such as smart page tooltips, drill-through et al in the most productive way.

Make sure you don't miss out on this incredible event! REGISTER TODAY by clicking on this >> LINK <<

 

Agenda at a glance:

09:00 (Start) 

  • Building your first report in Power BI
  • Introduction to Power BI, Zebra BI and IBCS®
  • Quick win: Power BI report from Excel data
  • Zebra BI visuals: How to use the charts and tables

10:30 (Break - 15 min)

  • Business reporting in Power BI
  • Actual vs. PY reporting
  • Calendar dimension, DAX time functions, YTD switch
  • Actual vs. Budget reporting (sales reports, cost budget reports)

12:15 (Lunch break - 45 min)

  • Best practice data model in Power BI
  • Essential data transformations in PowerQuery
  • Star schema: modeling tables and relationships
  • Top 5 DAX formulas for business reporting, dynamic measures

14:30 (Break - 15 min)

  • Financial reports and income statements
  • Hierarchy of accounts, subtotals, expenses, results
  • Tips&Tricks: Smart tooltips, Drill-through, Bookmarks, etc.
  • Best ways of sharing your reports & dashboards

16:15 (End)

Very much looking forward to seeing all of you there and getting to know you.

Event location:
Kontroling Kognosko
Jaruščica 1E,
10000 Zagreb
http://www.kognosko.hr/kontakt/

MsBizzAppSummit Atlanta Georgia

Zebra BI goes to Microsoft Business Applications Summit 2019

We are privileged and excited to announce that we'll be present at the Microsoft Business Applications Summit in Atlanta, GA., between June 10-11.

Joining a vibrant community for 2+ days of total immersion to drive better data, stronger solutions, and bigger transformation.

This great event is covering all things Dynamics 365, Power BI, Excel, PowerApps, Microsoft Flow, mixed reality, and more under one roof.


Our CEO Andrej Lapajne will lead a 20-minute session called "Building spectacular Power BI dashboards with P&L visuals and small multiples" on one of the biggest events this year!

 

He will demonstrate how to build advanced Power BI dashboards by using popular data visualization techniques like the small multiples, sales vs. budget charts and the IBCS® semantic notation.

While doing so he will be taking on the challenge of presenting user-friendly income statements (P&L) in Power BI. In that same session, he’ll take advantage of the Zebra BI custom visuals to build a real-life sales and financial dashboard from scratch.

Along the way, he will reveal practical tips&tricks for designing advanced, mobile ready and fully responsive dashboards.

Very much looking forward to seeing all of you there, getting to know you and saying hello to all of our friends, partners, and clients. 😊

Building spectacular Power BI dashboards with P&L visuals and small multiples
June 10-11, 2019

Event location:

Georgia World Congress Center
285 Andrew Young International Blvd NW
Atlanta, GA 30313

Click here for the agenda and more details about the event: https://www.microsoft.com/en-us/businessapplicationssummit

Burning Questions: How to Add a Monthly/YTD Switch to a Power BI Dashboard?

One of the questions we get most often by our users is how to create a switch in their Power BI dashboard that would let them switch between Monthly and YTD views. You can do this by using Data Analysis Expressions (DAX), which is the formula language used throughout Power BI. This blog post will provide a quick overview of what you need to do to create a simple switch.

Before you start, download the examples from our webinar page for Top 5 DAX tricks for super effective Power BI dashboards. We also recommend that you watch the video as it also contains detailed step-by-step instructions for preparing your data and tables that are not covered in this post.

Start by opening the Sales Dashboard example (SalesDashboard-ZebraBI-webinar.pbix) from the zip file you downloaded. Open a new page by clicking on the + icon on the bottom of the start page.

The example already includes all the tables, relationships between them and code, so you just need to recreate the basic sales dashboard you will add the Monthly/YTD switch to.

Recreating the basic visualization

Start by recreating the main visual in your report. Add a Zebra BI Power Chart and select the following fields: Group under Business Units, Month under Calendar, AC and Revenue PY under Sales. Set them to the corresponding Fields as shown on the image below:

Now combine various visuals. For example, you can add a comparison to previous year using another dimension. Add a Power Table and select the fields as shown:

This table adds a comparison of actual results to the revenues from the previous year by individual product groups and divisions.

This visual is fully responsive and also controls the small multiples visual on the right that you've created in the previous step. If you click on a category, the chart on the right will change to display just the selected category or group of categories (division). With this, you've already created a great visual. However, we can make it better.

Adding slicers to your dashboard

Next stop is adding two slicers: Year and Month that you can use to switch between views for individual months and years.

Click on Year field in the Calendar group and drag and drop it onto the dashboard. Switch it to the Slicer visualization and select Dropdown option from the menu.

Add another slicer for months. Click on Month field in the Calendar group and drag and drop it onto the dashboard. Switch it to the Slicer visualization and again select the Dropdown menu item.

This will break your visualization. To fix it, you need to decouple the time series view from your slicer. Click on the month slicer and select the Format tab on your ribbon. Select the Edit interactions tool.

This shows additional controls on the dashboard, where you can control what impact the selected visualization should have on the others. To prevent the currently selected visualization element from impacting your small multiples chart, you should select the no impact icon.

Adding the Monthly/YTD switch to your dashboard

Now, we will add the Monthly/YTD Switch. Switch to Data view and add another table for the switch on the  dashboard. Go to the Relationship view, where you will create what is called a "disconnected table" and serves as a switch between two values. Click the Enter Data button in the Home tab and name it Period Calculation in the Name field on the next screen.

You need to create just two columns - Period calculation and CalcID. 

You now have a new table in your project that is available for you to select in Report view in Power BI. Drag and drop the Period Calculation field onto the chart and switch it to a slicer element. You get two options - monthly and YTD. Switch to Horizontal orientation under General option in the Formatting tab.

You now have a slicer with two options (YTD and Month). You click between the two although the options do not do anything yet.

Create a new measure and name it Selected calculation. You will use the MIN function, which simply returns the smallest value in a column. Enter the following formula: SelectedCalc = MIN('Period Calculation'[CalcID])

All you have to do now is to link this back to your model. You need to take your AC measure and use the following DAX formula:

AC = SWITCH([SelectedCalc];
1; [AC filtered];
2; CALCULATE([AC filtered]; DATESYTD('Calendar'[Date])))

Just to explain briefly, what these functions do. SWITCH returns different results depending on the value of an expression, in this case AC filtered element in your project. If the value is 1, the expression returns the AC filtered value. If the value is 2 it calculates revenue to return the YTD value for which the DATESYTD function is used.

Make sure you download the free trial!

Try Zebra BI Visuals for Power BI

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

 

Top 5 New Productivity Features in Zebra BI Custom Visuals for Power BI

The latest version of Zebra BI for Power BI brings a bunch of new features that bring new flexibility to companies and users who rely on standardized reports to present their data.


Reorder your columns and scale your reports

As you work with Zebra BI Power Tables you might want to change the default order of columns. While the default ordering is based on best practices and standards prescribed by IBCS, companies very often have reporting standards which define a proprietary order of columns or simply need more flexibility.

For example, data for the previous year should be displayed to the left of Actual results or to the right. With the latest update, you no longer have to worry, just simply drag and drop columns to reorder them.

The reordering is very flexible and intuitive. Your custom column order is preserved even after you resize your table. Some detail, particularly charts, might get converted into numbers to save space and retain readability, however, the information itself is not removed and stays front and center. This feature is great for scaling your stories from beautiful, highly visual full-screen power bi dashboards to extremely compact, mobile-friendly reports and everything in between ... with a simple resize action

Use this feature to add a new level of flexibility to your IBCS-compliant reports and dashboards. Our visuals now support virtually any customization you might need to create powerful and convincing data stories.

Focus on the most important things

To make a point, sometimes you just want to show the most significant items and reduce the visual clutter. To help you do just that, we've added the Top/bottom N feature to our waterfall charts. Once you toggle the option On, you simply select the number of top or bottom values you want to display and the chart adjusts automatically.

 

How to use this option? Once you have your waterfall chart, click the Format tab in the Visualizations pane. Open the Categories group and toggle the Top/Bottom N switch into the On position. Afterwards, you can set the number of items that need to be shown. Other items are merged into a single group named Other.

This feature helps you focus on the most important data points while reducing visual density to improve chart readability.

Automated data labelling

Everyone hates it when the labels on their charts overlap and ruin an otherwise perfect visual. We've added an advanced algorithm to Zebra BI PowerCharts to prevent this. When you are working with a narrow chart that cannot accommodate all the labels, the visual will hide them automatically. Labels appear again once you resize the chart or switch to the Focus mode. This ensures optimal label density depending on your context and situation.

To turn this feature on, select your chart and click the Format tab in the Visualizations pane. Open the Data labels group and select Auto from the Density dropdown menu.

Small multiples that combine chart types

Small multiples mode now supports charts combining multiple chart types (bar and lollipop, for example). To use this feature, select your chart and click the Format tab in the Visualizations pane. Open the Chart Settings group and select either "Absolute / Relative", "Actual / Absolute" or "Actual / Relative" from the Layout dropdown menu.

At the same time, we've added the Top N + Others option to the small multiple mode. It works similar to the feature described above by showing only the most important charts and combining the others into a single chart named Other.

To use this feature, select your chart and click the Format tab in the Visualizations pane. Open the Small multiples group and select an item from the Top N dropdown menu. Then, set the number of items you want to have shown.

Break the axis in line and area charts

Sometimes you want to track a difference in a KPI that has very small variations, you can now break the axis in line and area charts to emphasize otherwise minor variances. The feature works on individual charts as well as small multiples.

Our advice is to be careful with this feature, because it artificially emphasizes differences where there might not be any. However, you might find this feature useful if you want to focus on the trend of a variance or a similar feature of your data.

You can find the axis break option in the Format tab in the Visualizations pane under the Axis break group. Turn the feature on with the toggle and set the Percent value - lower values might result in a better display of very low data labels.

Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Top 5 Power BI DAX tricks for super effective Power BI dashboards [Webinar]

Welcome to our webinar. I'm Andrej of Zebra BI, and today I'll present our 19th Zebra BI webinar, where we are exploring the world of reporting and dashboarding in Power BI and in Excel. This time, I'll present my top five favorite DAX tricks for building dashboards in Power BI.

Watch video recording and
download PBIX examples

In the previous webinars, many of you have asked me, "Oh, how did you do that trick? How do you do the year-to-date slicer? Can you change the measures dynamically in charts?" Tricks like that actually enhance the usability and the user-friendliness of your dashboards in a really massive way.

I have seen a lot of dashboards in Power BI, and the thing is, it's a fantastic product, and you can do a lot with it. I've seen so many colorful and beautiful dashboards that tend to have a few problems. First of all, sometimes they're just simply not effective enough. Secondly, they tend to get really complicated when you want to build something.

People are using bookmarks, they are hiding charts and putting lots of elements on the page, which is really complicated to build and even more complicated to maintain. That's why I've decided to share a few Power BI DAX modeling best practices and tricks which will hopefully speed up the development of your dashboard pages and reports and make them much easier to maintain in the future.

My agenda for today, quite simple. I'll show you my top five tricks. This webinar will be a little more technical and I will definitely try to build a practical sample of a dashboard in 40 to 45 minutes. Hopefully, I make it and I'll explain some of the DAX formulas along the way. I will focus especially how to use DAX and how to use these pieces of a DAX model in Power BI in a smart way to enhance your dashboards.

After the webinar, we will share all the resources and the download links. Our chief technology officer, Mitja from Zebra BI, is here with us and will help you in the chat. If you have any kind of question, you can just go ahead and type your questions and while I'm doing the presentation, Mitja will just try to follow up with you over the chat, send you all the links and so on. Otherwise, I'll just make a wrap up at the end of the webinar where we'll also answer your questions.

If there's anything unanswered, we'll do a Q&A at the end, so just type your questions into your questions box. We will send you the recording, which will be available tomorrow or the day after tomorrow, along with all the PBIX examples that I'm gonna build today and the Power BI DAX code.

Looking around Power BI

I'll just switch to Power BI now and show you a couple of examples that I'm trying to build right now. First of all, we'll start with some basics, like just getting your time series right and getting your calendar right, things like your months, quarters, years, days, weekdays and others. The core element of every Power BI DAX model is actually a good calendar dimension, so we'll just start off with the basics.

Once we have that, we'll gradually start to build things like actual versus previous comparison. We'll calculate the previous year values and do the comparison. For example, here I have my actual value. We are looking at the sales revenue and this is the previous year's sales, and this is my growth from previous year in absolute terms, and this is my growth in previous year in percentages.

Power BI DAX Example: Sales vs PY

You can do a monthly comparison versus previous year, which is a very basic concept but you need to know your DAX to do this in Power BI.

The next thing is the switch. It's a very simple way to switch from monthly to year-to-date results.

Power BI Dashboard Example: Sales Monthly and YTD

If you have a monthly development like this and you compare it to the previous year, it's really not obvious what's going on. You see monthly values, but how does this accumulate to year-to-date values? What's the final result at the end? If you do have a year-to-date switch, you can actually change the view like this so you see the accumulated values. It's a comparison of actuals versus my previous year.

You can also add the slicers that affect the whole dashboard page. I'll show you how to build this in quite an easy way.

I am currently basically working with just two visuals. Everything else is just done in the model itself so you can switch to the year-to-date view. You can also switch your KPI. Right now, this is the revenue and I want to see the whole page for the gross profit. You just need to click on the gross profit and everything will change to show the gross profit. We'll build a few pages that just take advantage of this model. Using the same model it's easy to build different views, monthly views, trends, structured tables and comparisons.

We'll add the comparison to the plan. These are like the three most important categories: previous year, actual and plan. You can build nice variance analysis reports like this one, which are completely dynamic, so you can just dive into the details.

You can also change the order of elements. You can have the previous year, actual and plan, and then you can put the variance in between. You can just shuffle things around and reorder things a little bit. You've built yourself a nice variance report for the month of September.

Starting from scratch

Let me start with a completely empty Power BI file and just try to build at least maybe 70% of what I've shown so far.

Let's start with a completely blank Power BI document. The first step, of course, is just to get some data. I have my data in an Excel spreadsheet, so I'll just connect to my Excel.

I am using my sales demo database. I've got a couple of tables here and here's my sales. This is my sales data - three years worth of sales data from 2016 to 2018. It covers daily sales, so I have a date field here, and then I have some product IDs, customers, sales persons, business units, and so on. I have three major KPIs or measures: the revenue, the costs, and gross profit.

I use an additional field that's in a column called scenario. This indicates whether this is actual data or planned data. It's all joined together in the same table. We call this a fact table. Then I have typical dimensions, like and business units, such as divisions, groups and so on. There is also lots of customers from different regions and countries.

We are also working with products, sales persons, account managers and so on. I'll just load everything into Power BI and we'll start analyzing this, trying to build a model and make sense out of it to gain some insights. We'll prepare a nice dashboard that you'll be able to share with end users and your management. After everything is loaded into my Power BI, I can switch to my data panel here to see all my tables.

Looking at the relationships between tables

The tables include business units, customers, products and so on. Switch to the last tab showing the relationships between tables. This is my model and it should not look too confusing. I still think that the so-called star schema model is the best way to model things in Power BI. It's a best practice that when you load data into Power BI you try to plan for a star schema. This means that you have one big facts table, the so-called fact table, with all the facts and measures. Then you can build all your dimensions around it.

Power BI DAX Model: Star schema

Power BI has already created some relationships here and let's to explore this in Power BI.

I can select the Revenue field and I should also select the Date field. Here it is. I'm doing some sort of analysis with native Power BI visuals. You can see the trends based on the yearly values. You can drill down into this but it's hard to make sense out of this data. You can see that there's sort of a yearly trend and that revenue is improving over time.

But it's really hard to see what is going on. The labels are problematic here, and maybe the best solution would be to just use the line chart. But this is really not an analysis. Once you start building reports dashboards, you have a goal in your mind, especially in monthly reporting. I'll try to build a monthly report. First of all, you need to add a comparison. Once you compare one value with another value you start to understand what is going on: is this better or worse than planned? How much better, how much worse? Is it better than the growth from the previous year?

Once you have the comparison in your model, then you can actually start analyzing things and communicating them. We will need to build the comparisons and everything else. The first thing we need to do is work on the date values. You see that if you just have a normal date field here, you will see the trends and so on, but that's more or less what you can do. We will return to our model and work on the calendar dimension.

Setting up the calendar dimension

Every good model in Power BI starts with the calendar dimension, and we'll switch to the data view to show you how to do this in DAX. We'll start working with some DAX formulas. Just switch to the modeling tab, and we will create a new table. I'll just call it Calendar and while I'm building this, you'll get the idea why this is important. This is my first Power BI DAX function for tonight. Just a calendar. There's a function called CALENDAR which actually builds a list of dates, so that's your first option.

Let's build a calendar from the first of January to 2018 - that's the end of my data - end of 2018. This will return a simple column called Date.

Calendar = CALENDAR("2016-01-01, "2018-12-31")

That's exactly what we already have in our sales table. I'm doing this now so that after I have this column, I can add new columns. I clicked the New column button and now I can add my own time functions to create new columns. The first one we'll just call Year, and we'll just extract the year from the date. That's really simple to do.

So, these are the pure basics for those of you who are more or less beginners in Power BI. The function year will extract this for me, and I'll just refer to my date field here. This simply returns the year, here. So, the YEAR function just returned the year. Next I'll just do this in a format. Use the FORMAT function to format the date in a number of different ways. To get the year you just type the year and four Y letters. This formula returned the year, but this time it's as text.

"Year", FORMAT([Date], "yyyy"),

Now we'll add another column. We'll call it Month. We'll use the FORMAT formula again. Format the date, and this time we'll use three M characters.

"Month", FORMAT([Date],"MMM"),

This is how you can build your custom columns which you will use in your reports. Let's add the quarter, as well. I'll use the format date to extract the quarter and use Q for quarter. This returns a number between one and four. Just a number. If you want to have values like Q1, Q2, Q3, and so on, you can use it. You completely control the labels and how the labels look like.

You should add the letter Q in front of the number and use concatenating. You can just type in the text Q and then join this with the end operator that provides the number. Enter this and you get Q1.

That's the standard way in Power BI. Most of people do this. There's a even easier way; you can simply add the letter Q that you need to escape it. If you do it like this, the backslash displays the letter Q, which is followed by the number of the quarter.

"Quarter", FORMAT([Date],"\QQ"),

There are other functions, like weekday, weeknum for week number, and so on, that you can use to build other columns in your time or date or calendar dimension.

Using CALENDARAUTO

There's another way how to do this. I'll show you an alternative way in DAX, how to do this in Power BI. There's actually a function which can help you. It's called ADDCOLUMNS, which can actually add all the columns within this function. You use the ADDCOLUMNS function to say the calendar will use the CALENDARAUTO function.

Then just add all the columns at once. I'll simply copy the whole formula here. I'll create a new table like I did before, but this time I'll just build everything together. Just paste it inside, and this is the Power BI DAX that I'll share with everybody. This formula has built the whole calendar table with all the columns and you can tweak how do you want your years to look like. You decide whether you'll use four numerals or two numerals? The CALENDARAUTO is a version of the CALENDAR function which automatically searches for the dates in your database and automatically finds the minimum-maximum date and just builds everything automatically.

Calendar = ADDCOLUMNS ( CALENDARAUTO (),
    "Year", FORMAT([Date], "yyyy"), 
    "MonthNo", MONTH([Date]), 
    "Month", FORMAT([Date],"MMM"),
    "Quarter", FORMAT([Date],"\QQ"),
    "YearMonth", FORMAT([Date],"YYYY-MM"), 
    "WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat
    "Weekday", FORMAT([Date],"ddd"), 
    "WeekNo", WEEKNUM([Date], 2),
    "Week", "W" & WEEKNUM([Date], 2) )

This'll work like in 80% of cases. Otherwise, there are other options to build your calendar dimension in Power Query, which is another part of Power BI that I don't intend to cover today, because my title is DAX tricks in Power BI. What have we gained so far? We've got the calendar dimension and instead of using the date field from your sales table you're using the calendar dimension allowing you to be much more flexible. For example, you can now say: "I want to see my sales by year."

However, this is still not working. There is no yearly trend and the entire chart is flat. Why is that? Because we have a new dimension but it's not linked and not related to my data. You need to click here on the Date field and just create a relationship to the sales table. Link the Date field here to the Date field in the Sales table. I will actually hide the Date field in the Sales table, because from now on we will not use it in the report anymore. Instead, we'll just use the calendar dimension. Now this is working but the sort order is not okay.

This is a common questions that we get from people who are starting Power BI: "Why is my sort order not correct?" For the years on every visual you need to switch to your field that you want to sort by, and select Sort Ascending to sort the years correctly.

If you do this for the month, there's another caveat. If you have a monthly view and you sort ascending, you don't get the right sort order. This is just an alphabetical sort order, so you need to tell Power BI how to sort the months. These are things that look completely obvious to every Excel users but these are real, real challenges that people are facing when they start using Power BI.

You can solve this straight from your report view here. You click on your month and then go here to click Sort by column button. Open this to find your month number, which is one column in your calendar dimension. This chart is now sorted by the month number. Now we can start analyzing data.

Switching to Zebra BI for Power BI

We can start building dashboards. To do this, I will first import Zebra BI visuals. At the moment I'll just import two visuals from Zebra BI, which we are developing.

Now we have the revenue by month, and I have other fields and I'll use the year as a slicer here. This allows the end user to choose the year, which is a basic thing in a report. Drag the Year measure onto the canvas and switch to the Slicer visualization and I'll set it as a dropdown menu. This'll be just my first selection here, my first slicer.

So far, this is still quite boring. I can change the year and there's some results here. This is still very basic, just some monthly developments and trends. You just just see basic things like: "Is it better in the summer or better in December?" or something like that. You can see some trends and outliers and things like that, but that's just not enough. We'll start by comparing this to previous year.

Now that we have the calendar dimension, we can actually add another field for the previous year, which will calculate the previous year. On top of the revenue, we'll look at the revenue for the previous year. First, I'll do something else. What we are looking at here is not really our revenue, because I actually have two types of data in my sales database. I have plan data and actual data together jammed into the same table. It's in this column called scenario. So if I just add the scenario here, you'll see the actual data and the plan.

So I need to, first of all, separate actual results from the plan. I will build my first measure here and we'll just click new measure.

We'll call it Revenue AC. This is the actual part of my data, and I'll do this by first running a SUM function on all the values of the revenue column. That would be just a normal measure without any kind of filtering. Now I need to filter it. I will use the formula called CALCULATE. The formula will take the sum of my revenue field and then filter this.

I'll use another formula called FILTER, which will actually filter my sales table. I type in Sales for my sales table, and I'll use the filter expression. I'll use the Scenario column. I will add my condition here and the scenario column should match actual to display my actual revenue here.

Revenue AC filtered = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Scenario]="AC"))

I will not use just the column, but I will use this measure in my chart. I'll throw out the old one, and I'll use the actual values. We are now looking at my actual sales in December of 2017. Now that we have this, we can also create another measure for the previous year.

I'll just take the actual revenue and just shift the year by one to look at the past year. You can do this by using the CALCULATE formula again. Here's the formula:

Revenue PY = CALCULATE([Revenue AC], DATEADD('Calendar'[Date], -1 , YEAR))

This takes my actual revenue, which we just calculated before, and now we'll use the DATEADD function, which is kind of the most versatile and just safest to work with. Otherwise, you can try other ones. There's actually a formula called PREVIOUSYEAR, which is easy to use, but has some caveats. Then you can use the same period last year and the parallel periods and a few others. But I just do this, use DATEADD to take my dates from the calendar.

We'll just shift it for one year back which is why we use minus one in the formula. That's it. This formula takes my revenue and just shifts it for one year back. Close it and we have the revenue from previous year. Now you have the the basic conditions, the minimum model in Power BI to start building your comparisons. Now I add this revenue from previous year into my visual, and now the magic starts to happen. Only now can we start building meaningful reports in Power BI.

Looking at variances

As you may have noticed, the visual has changed completely because now our visual understands that you are working with two values. You have actual values and the previous year. Our visual calculates the sum of the previous year for the whole year 2017. It is 6.5 million. On the other side, we have the actuals at 8.1 million. The visual already calculated the growth and now everything else happens automatically. You have the growth from the previous year expressed in percentages and if you click it, you will have it in absolute values, or you can click again to display both. You now have year over year values for each variance for each month.

You can just simply switch between different types of charts and just do a simple line comparison. Or you can use area charts and column charts with different types of layouts, here. So, if you have very limited space, you can just use a column chart with the variances. This is now the growth for each month, here, and if you enlarge it, the visual will calculate the relative growth for each year. You can see the relative variances here.

If it's even larger then, you can display it in a much, much better way. This is really nice, because you can have a very small chart here and other things around it, but the the user can then simply open a chart in the Focus mode where everything is big and the relative variances are calculated.

My personal favorite here is a waterfall chart. It's good, because it just focuses on the most important things. If you want, you can break the axis just to temporarily see the variances better. Otherwise, just don't do that.

Combining multiple visuals

Now we can start building dashboards by combining multiple visuals. I enabled the previous year comparison and I can already create a dashboard page. For example, I'll take another visual and I'll just do a comparison to previous year by another dimension. We'll use the group of business units and do the comparison of actual versus previous year. You can again see that our visual is fully responsive. In a single chart you have the actual, the previous year and the previous year in percent. You can do very interesting analyses already with the two visuals and previous year comparison. For example, you can sort and see the worst performing business units and then click on it to see the details in the monthly details.

As you may have seen, we have very different business units here: baby care, wearables, audio hardware and so on. It's kind of a mixture, because this is a company that deals with different types of businesses, and that's why it would help to add another hierarchy level here. I'll just add the Division field into category just to understand this business better. As you see, we have electronics and then some personal care. We see that this is a completely different business here.

You can now just click on the level to see the result for electronics, the wearables and mobile products and so on. This is already starting to make a little bit of sense. An even better decision is not just to have one chart on the right but to present all the business unit groups here. You can do this by just adding the group to the chart. Let's make the left one a little bit smaller, and the right one a little bit bigger. This is just another way to build a dashboard page.

Now you see the trends for each one. The right one is the small multiple so you see the trends for everything. I'll switch back to my favorite, the waterfall chart, which will look much, much better if you clean it up with proper labels. You need to figure out your units. I'll just report in thousands. It's much better to just use the K, M, and B suffixes, which Power BI always puts in as default. Simply put them in the title. Now we have revenue and revenue versus previous year comparison shown in K.

I'll also reduce the number of decimal places. In some cases, you can also reduce the label density to make things cleaner. Some charts need a little bit more space, but you can actually control this by setting the category width. You can control the category width by setting the fixed category width. This allows you to say the maximum width of your columns is 22 and make all the columns exactly 22 pixels wide. Now this chart makes sense. Now the user can analyze data to just see all the business units or just look closely at what's going on within electronics.

I still prefer the waterfall chart. You can also crop the axis or just click on the personal care. You see these four product categories and it's obvious that you have one big business unit here in your personal care division. If you have data visualization challenges like this - with big and small charts - you can use the advanced layout in small multiples. The automatic layout will try to see if there are some large values and will try to arrange other charts around it for a better placement. You can also play around with the so-called largest-first algorithm, which will just take care of really big charts. This usually provides better results if you have large differences between data categories.

Creating the YTD switch

This chart is already starting making sense but we need to move forward. The next thing would be to create the year-to-date switch. Year-to-date switch will use the month and I'll take the month and use the month as a filter on the page by using the Slicer visual. The user can simply select their month, like August. Of course, the slicer filters all the visuals on the page, so when you switch to September, the table part of the dashboard is okay but the trends are broken. Fix this by clicking on the slicer, selecting Format settings and selecting the Edit Interactions button.

You want this slicer to control everything but the time series. When a month is selected in the Month slicer, it's not affecting the time series. This is just the simplest way to control which visuals are affected by a slicer.

Now we need the switch. The switch is used so you can look at data for August or August year-to-date. How to do this in Power BI? Let's switch to the data view where we will add another table.

Or we can do it in the Relationship view. We'll add another table for the monthly versus year-to-date switch on the dashboard. This is called a disconnected table, because it's just going to be a switch with two values, and we'll do it by clicking Enter data and we'll just add another table which I we can call Period calculation.

Let's add the first column. This will be my period calculation. The next one will be a number and we'll just call it calculation ID. We'll put "monthly" in the first column. This is the first option, option number one. Option number two is year-to-date. Number two. We created this little table here and it should be placed somewhere near your calendar.

When you go back to your model you have the period calculation column in the Fields list that you can just move to the dashboard. You now have have monthly and year to date options. Change this visual to a slicer again, but make it horizontal this time.

Now you have a nice little button here where you can simply click on monthly or year-to-date values. Of course, nothing is happening yet, because it's not related to other visuals, so you need another Power BI DAX trick here. Create a new measure and call it Selected calculation which will simply return the selected calculation. I'll do this by using the MIN DAX function to return the minimum calculation ID. Here it is. Alright, and that's it. It's really simple.

SelectedCalc = MIN('Period Calculation'[CalcID])

I'll just drop this into my report and display it as a Card visual. It's working. How will I link this to my model? I will go back to my Revenue AC measure, which at the moment simply returns the filtered column from my sales fact. I'll do this in two steps. I will just rename this measure. I'll call it filtered - Revenue AC filtered. Now, I'll add a new one that will take into account the selected calculation and if the value is 2, calculate the year to date value.

This will be my true Revenue AC measure. How do I do the switch? What's the function name for that in DAX? Switch.

Revenue AC = SWITCH ([SelectedCalc],
    1, [Revenue AC filtered],
    2, CALCULATE([Revenue AC filtered], DATESYTD('Calendar'[Date])))

You evaluate the expression. In my case, I am going to evaluate the selected calculation. Let's go into new line by pressing SHIFT + ENTER. And now, if the value is one, the formula simply returns my previous measure, which is now called Revenue AC Filtered.

In the second case, when I need the year-to-date value, I will calculate my Revenue AC Filtered. I need the year to date value and there's a function for this that's called DATESYTD. That's year to date, and you just need to add the date column from the calendar table and we close it off. Now my Revenue AC measure just evaluates the selected calculation from my switch, and does the switch, and calculates year-to-date if necessary.

This measure is prepared. So, let's click on the visual, I'll just throw out the old one, and throw in the new one, and hope it works. It's very green because now I'm comparing my actuals. This is now year-to-date, and comparing to my previous year. Previous year still refers to my old measure, so I'll just correct this. Previous year will be calculated from the new one, so it'll work for monthly and for year-to-date. You see, all these different options. How to display the year to date.

Let's fix the design by removing shading by setting opacity to 0%. We need to fix the table by using Revenue AC measure, which takes into account the monthly, the year to date, and will work across the whole dashboard. All of this is happening in the same visual. There's just one visual and everything else is done with switches and dropdown menus.

Creating a KPI switch

That's basically all of the Power BI DAX that I wanted to show today, but I do have one more trick. Trick number five, where we'll use the same idea with the switch, but this time I will use it to select the KPI.

We want to change the charts from revenue to gross profit. Right now, we have done this for revenue and we have revenue, actuals and previous year. I could also do this for the plan. Let's do the plan quickly. I will copy the existing formula for revenue and simply filter by the plan. This'll be my revenue for the plan. Click New measure and use the below formula.

Revenue PL filtered = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Scenario]="PL"))

So, this is my plan filtered, and now I need the plan, the measure for the plan, that it'll also take the account the same switch. Create a new measure and paste in the below formula:

Revenue PL = SWITCH([SelectedCalc];
    1; [Revenue PL filtered];
    2; CALCULATE([Revenue PL filtered]; DATESYTD('Calendar'[Date])))

This is the switch. This will return the normal plan and in the second option it'll return the year-to-date values of my plan. Now I've got the actual, the plan and the year-to-date. However, all of this is just for the revenue. Imagine if you wanted to do this again for the gross profit, one option is to do all of that again for the gross profit. Same thing just for different KPI.

You could just copy and paste the same DAX for the Power BI measures. But if you have five, three, five or ten measures, that's a lot of measures. And another thing is when you want to use those measures on your report, you need to create another visual, so you need one visual for the revenue, the second one for profit and so on. But I'll do something else. I will use a switch and I'll just reuse the same measure.

The idea behind it is to add another disconnected table. Let's do the same here. Click Enter data in the data section and repeat the whole procedure from before. This time I will call this my KPIs table. My first column is the KPI, and the second one will be a number: one, two, three. I have three KPIs, so I'll just name this KPIID. My first one is the revenue, and this is KPI number one. Then I have my cost. That's number two, and the third one is the gross profit. This is now number three.

I load this into my model and I've got another disconnected table. I can use this KPI now as another switch.

I will change it to a slicer visual. I want this to just reflect in my dashboard. Make this into a dropdown because it's nicer and it'll fit the top navigation bar. I'll create a measure that just returns the selection, and I'll call it SelectedKPI. Again, I will use the MIN function , which will return the minimum KPI ID, like that.

SelectedKPI = MIN(KPIs[KPIID])

This is now my selected KPI. We can switch between revenue, gross profit and costs. The order here is not really what I wanted. Everything is sorted alphabetically in Power BI by default so I just need to set this in your Data view. Select the KPI table and set that KPI needs to be sorted by column KPIID, which will now get the measures in the right order.

Revenue is number one, cost number two, gross profit number three. That's the custom sort, exactly how I want it. Now, I will just use another SWITCH formula to return the measure that I want.

I will create another measure. I will call it Selected value. The below formula will calculate the sum of my revenue as the first option. The second option will return cost. Number three is the last one and will return gross profit. This switch simply returns a different column based on the selection in the slicer.

Selected value = SWITCH([SelectedKPI];
    1; SUM(Sales[Revenue]);
    2; SUM(Sales[Cost]);
    3; SUM(Sales[Gross Profit]))

This is now my Selected value measure and we just need to correct my measure from a revenue actual to a generic actual value which will return not the revenue actual but the ... Actually, I have a better idea. I can do it here. Instead of returning actual for the revenue, I will return the actual for my new measure Selected value. And that's it. This is now not revenue anymore, it's actual data filtered and it uses the selected column.

This is AC filtered, and now I go to my revenue AC, and this is already working, I will just rename it. This is now my generic actual measure that takes into account the year-to-date and the selection of the KPI, the selection of the column. Let's see if this works: Gross profit revenue. It's working.

Putting it all together

And now this is a completely generic dashboard. It works for everything, and you can simply use one visual and everything will work. Now you can create pages in minutes. Let's just put those slicers in order. In a dashboard, I would first put the revenue, then I would put the month, and then the year, and then my period calculation. If you want to save your dashboard real estate. You can remove some of the headers for month, year, and so on. You can simply put the slicer header off and you could just keep it completely minimal and use the space for something better, which is the visualization tables and everything else.

You've got your page and you can simply just copy the page and use the same thing for something, you know, for different types of things. For example, let's make this a big one. You can add the plan and now you can build tables and compare previous years and actual plans by including variances that you can just reshuffle around. You can do this on this nice little page with only one visual.

You can add details like business unit and add more levels. You can create a hierarchy, like a multidimensional, multi-level hierarchy. You can even do crazy things like removing the slicer. What happens if you remove the slicer? Everything still works. That's the magic of the MIN function, because even if the slicer is not there, the minimum is still evaluated, and it will return the option under one.

I just wanted to show you something else. Instead of showing the business units under Group I will use my KPIs. I'll put my KPIs into the Group data field and now I've got small multiples of the measures. This is something that I think is completely impossible to do in just vanilla Power BI, because now I have my revenue and I have my gross profit, basically, scaled, in a small multiple. This helps you understand what is the relation of gross profit to revenue? I can also filter this by revenue and gross profit for comparison. You can also do two-dimensional multiples, multi-dimensional multiples.

To do this I'll just group by another field here, which creates the two-dimensional small multiples. I've got revenue and gross profit for all my business units, or divisions, and so on. YXou can switch from monthly to year to date. You can compare the revenue and gross profit and everything else. It's very easy to build dashboard pages and you can just create 10 dashboard pages in like an hour or something.

Conclusion

These are my top five Power BI DAX tricks for today. That was it. I hope you enjoyed it. I will share all materials with you in the recording. Download Zebra BI visuals. This is the download link, so there's a free trial available. If you want to try this on your own, please just go to this website. If you don't have them, download them, and you'll get the free trial so you can play around. We've got the resources on our web page. We've got a help section with a lot of PBIX files, so Power BI files that you can simply download from the previous webinars and income statements and other stuff. It's quite interesting.

And we've got the webinar recordings on this link, so make sure you check the webinar recording. There's 19 hours of the webinars, of the recording material, already, on this link. So all our previous webinars, from Power BI, Excel and reporting. And if you need help, just shoot us an email at [email protected]. Somebody will help you, and follow us. If you like this, if you like the ideas, please follow us. You know, mention us, please. Just help us spread the word about this, and make sure you're subscribed to our channels and so on so you'll get notified about the next webinars, and videos, and other stuff that we are doing.

Hope you've enjoyed this one. Thank you very much, see you at our next webinar. Bye.

Watch recording and get PBIX examples

Enter your data below to get immediate access to the full 1-hour video recording of the webinar and all PBIX examples used in the webinar.

  • I agree to getting instant access to the webinar and receiving educational content from Zebra BI.
  • This field is for validation purposes and should be left unchanged.

Privacy Policy

 

USAFacts Relies on Zebra BI Visuals to Open US Government Financials to Public

April 17, USAFacts released a 10-k report on US government financial using Zebra BI Visuals for Power BI. The shareable reports will drive interactive storytelling and deeper understanding of complex data.


USAFacts is a fascinating project funded by Steve Ballmer, former CEO of Microsoft. The project's aim is to make vast amounts of US government data more open, accessible, transparent and easier to use. Engineers and researchers collected and organized 30 years’ worth of data from US Treasury Department, the Office of Management and Budget, the US Census Bureau, and the Federal Reserve. They then published it on a well-organized online hub that is neutral and focused on the thing that matters most – impartial data.

On US Tax Day, the initiative launched the 10-K report for the US government – in the US, 10-K is an annual report that gives a comprehensive summary of a company's financial performance. USAFacts did that for government data.

To make their findings better accessible to the public, they turned to Zebra BI visuals on Microsoft’s Power BI platform to create some of their interactive visualizations:

You can also click "Chart view" below to see all of the visualizations interactively:

Zebra BI visuals for Power BI are a perfect fit for a project like this. Not only does it include a host of crystal clear visualizations and features, (chart slider, small multiples and native support for IBCS), its design is completely responsive, so it can be accessed on mobile devices or desktops. It can also be embedded on other websites to drive engagement. All of this makes Zebra BI visuals for Power BI the best choice for data-driven storytelling that uses clear, easy-to-understand and attractive visuals for communicating insights.

You can also find out more in Microsoft’s Power BI team announcement.

We are proud that the USAFacts team, in partnership with the Microsoft Power BI data journalism program management, has chosen our Zebra BI visuals for Power BI to bring the US government financials closer to the public. In many ways, this is for us an essential confirmation that we have have done a good job with our latest product and that our solution is a great choice for presenting business and financial data in a clear and understandable way.

We are an avid supporter of open data. Data transparency and clarity of business communication are at the very core of Zebra BI’s beliefs. It’s what we stand and work for.

USAFacts is a noble initiative and we are looking forward to supporting more projects like this!

Andrej Lapajne,
CEO at Zebra BI

Zebra BI Visuals for Power BI 2.0 Released!

Zebra BI Visuals, the only IBCS®-certified software solution for Power BI, has just been updated to version 2.0.

This release is available for existing subscribers as well as a free trial and delivers improved comparisons and more options for formatting and designing data.

Note: Version 2.0 of Zebra BI visuals for Power BI takes advantage of the latest additions to Power BI core, so it requires August update of Power BI Desktop. Older Power BI versions still work with version 1.3.

The most important new features of version 2.0. include:

Report page tooltips

Now you can make your Power BI reports even more user-friendly. Use report page tooltips to display additional information when a user hovers with his mouse over a specific data category.

Here's an animation of report page tooltips in action:

More info about using report page tooltips with Zebra BI visuals for Power BI is available here: https://zebrabi.com/pbi/use-report-page-tooltips-provide-details/.

Multidimensional small multiples

The small multiples functionality of Zebra BI visuals for Power BI has been improved. Watch the video below to see multidimensional small multiples:

Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Mastering Variance Reports in Power BI

One of the most important features in any business dashboard is the ability to present variances in a clear and easy-to-understand way that makes an impact. In this article, we explain the different types of variance reports and how to set them up in Microsoft Power BI.

Essentially, variance reports show the difference between the planned or past financial outcomes and the actual financial outcomes. No organization can expect to make informed decisions without having this insight and variance reports are all about providing it.

When you are working in Power BI, the default set-up lacks some of the features you need to create variance reports. However, Zebra BI has worked really hard to create Power BI custom visuals that help you tell the most convincing story. Variance reports are a big part of that and we will look at how to use Zebra BI to create them since many users still have issues creating them. Read more

Microsoft income statement – FY18 Q4 financial results in Power BI

On July 19, 2018, Microsoft Corp. announced the financial results for the quarter ended June 30, 2018, as compared to the corresponding period of last fiscal year.

The results are impressive, for example:

  • Revenue was $30.1 billion and increased 17%
  • Operating income was $10.4 billion and increased 35%
  • Net income was $8.9 billion GAAP and $8.8 billion non-GAAP

But what caught my attention, was the fact the press release included only standard tables with additional downloadable Excel, PowerPoint, and Word documents.

So my immediate thought was, where's the Power BI presentation of these fabulous results???

So I tried to redesign the tables in an attempt to present the financial results in a clearest possible way, by leveraging the amazing Power BI capabilities, together with the advanced visualization capabilities of Zebra BI visuals for Power BI.

Alright, here we go:

 

View as PDF

 

What do you think? Please let me know in the comments!

 

Note: This is not an official presentation of Microsoft Corp. financial results. It's just a quick study of how financial data such as income statements can be visualized in a clear and understandable way in Power BI as an alternative to standard data tables.

Source: Microsoft Corp. Earnings Release FY18 Q4

Zebra BI visuals for Power BI can be downloaded at https://zebrabi.com/pbi

 

Zebra BI Visuals for Power BI 1.3 Released!

Zebra BI Visuals, the only IBCS®-certified software solution for Power BI, has just been updated to version 1.3 that brings several exciting new features that make it easier to create convincing and clear visual business presentations in Power BI.

This release is available for existing subscribers as well as a free trial and delivers improved comparisons and more options for formatting and designing data.

Let's browse through some of the new features in version 1.3:

 

Pixel-perfect profit and loss layouts with waterfall charts for improved readability

Support for multiple scenario comparisons that delivers the highly requested actuals vs PY vs plan reports

    

 

Ability to display yearly totals next to monthly charts within the same visual

Additional improvements to small multiples

By setting the advanced layout options for small multiples and by controlling the minimal width of data categories in charts, you now have even more control over creating high-density displays of your data. Make sure you check out the 'Auto' and 'Smart row' datavis algorithms in Small multiples section and try out which sort order best suits your needs:

 

Significantly upgraded displays of hierarchical tables

Hierarchical tables became even more powerful. For example, check out this P&L statement with comparisons to PY and plan at the same time:

 

Along with many other improvements and new features, Zebra BI Visuals for Power BI v1.3 allow you to build even more powerful business reports and dashboards. Here you can browse through a few more live examples, published directly from Power BI using the Publish to Web function:

 

Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Zebra BI becomes the first IBCS®-certified reporting solution for Power BI

Good news for everyone searching for a way to bring IBCS®-compliant charts and tables into Power BI!

Zebra BI Visuals for Power BI has achieved a major milestone by receiving the IBCS® certificate to ensure that business information is presented clearly, concisely and consistently across the cloud, mobile and desktop versions of Power BI. This achievement is another step towards the vision of delivering meaningful one-click visuals to users where they need them the most.

"We've worked hard on Zebra BI Visuals for Power BI as we wanted users to have access to the best visual design elements they need for powerful data storytelling," said Andrej Lapajne, CEO, Zebra BI. "We see Power BI as a powerful business intelligence tool of the future and having our product IBCS®-certified underlines our commitment to the platform."

Here are the examples from the certification:

IBCS certification process is carried out by BARC, the independent research and consulting institute for business software. The examination is carried out in two stages: compliance with IBCS® and ease of use. In the first stage, software providers need to show the required level of compliance between visuals delivered by their software and the required standard as prescribed by IBCS®. In the second stage, the software is evaluated in terms of how easy it makes creating new templates from scratch without programming and using default features.

The certification team was most impressed by what we would like to call 'business-responsive design'. Zebra BI Visuals for Power BI dynamically create IBCS® compliant charts providing maximum business insight within a given space.

Jürgen FaisstManaging Director, IBCS Association

Try it yourself

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Build Spectacular Power BI Dashboards using Zebra BI custom visuals

Power BI is Microsoft's business intelligence solution in the cloud and on the desktop. It is a very interactive platform that allows you to create your Power BI dashboards from a wide range of data sources, including Excel, relational databases, OLAP, Analysis Services, Hadoop and even social networks, such as Twitter and Facebook.


Of course, Power BI features many charts and visualizations but unfortunately it doesn't allow you to build advanced, information-dense business reports and dashboards out-of-the-box. There's no way for you to follow IBCS standards, create advanced waterfall charts, difference highlights, small multiples, advanced options for managing outliers, etc.

Fortunately, Power BI allows you to import custom visuals, which opens you to a world of new possibilities. 🙂

But first, let's start with the basics...

First look at Zebra BI custom visuals

In Power BI, you are working in workspaces, where you organize your reports, dashboards and data sets. You will be spending most of your time working with reports to create visuals that you can then pin to your Power BI dashboards for quick access. Take a look at a sample report, created with Zebra BI custom visuals.

Power BI Dashboard created with Zebra BI custom visuals

This report has multiple elements - a waterfall chart, numeric indicators, sales by month and by store chain and categories. It is an information-dense page telling a complex story. The first thing you'll notice is how interactive this report is. You can essentially click on any part of data to filter other data or find out more. For example, when you click a specific month on a chart with monthly totals, other charts change to reflect to show just data for that month. You can also click on variance values to change them from absolute to relative or to display both values.

Trying to convey the same information with default Power BI charts would result in unwieldy stacked or comparison charts that are difficult to read. The waterfall chart is the easiest way to convey this information. In Zebra BI custom visuals, you simply need to add the actual sales and the plan and the chart automatically adjusts to reflect the values. In the next step, you just add months to create an interactive IBCS-compliant chart automatically.

Tip: You are not limited to a single type of chart. By clicking on the left or right arrow on the edges of the chart, you can slide between different types of charts and select the most suitable one.

Small multiples in Power BI

Another visual that shines in Power BI dashboards are Zebra BI's small multiples. This powerful method puts multiple scaled charts on a single page. In this example, a single glance will tell you that men's fashion is the largest category and how it compares to others:

Small Multiples in Power BI

The interactive nature of Power BI comes handy in this view as well. For a better understanding of data, you can exclude specific charts with a couple of clicks. You just need to hold down CTRL and select the charts you want to view. Visuals then adjust automatically for a better viewing experience - charts become larger and more readable.

Small Multiples are great for interactive Power BI Dashboards

This report also features a great example of a slicer (in the top right corner). A slicer narrows the portion of the dataset shown in other visualizations in a report. For example, you can use slicers as an alternate way of filtering to easily add or remove months from the charts in your reports.

Tip: Once you are happy with a specific chart that you want to view often, you should pin it to your dashboard for easy access.

Diving into detail

You can also visualize more detailed data. This report, for example, breaks down the sales by individual stores.

A Power BI Dashboard with charts with vertical axis

Again, data in this interactive chart can be easily sorted by variance or actual values. By clicking on the name of an individual store, the left-hand side displays more detailed information about it. The smaller chart below shows which product category is responsible for the failure to attain the plan.

There are several great visual details here. In the smaller bar chart on the left, you can see that the lollipop circle has been replaced by an arrow with a label. We use this approach when a value is such an outlier that representing it to scale would throw off the visual balance of the chart. These outliers often occur in cases where very small absolute values have very large relative increases or decreases.

Moving on to dashboards

Now is the time to take a look at Power BI dashboards. The visuals in your reports that you have been pinning are placed here. This is s single place to look at your data - from sales and financials to marketing and social media. Therefore, you can select individual visual to act as KPIs. The design process is also easy and very intuitive. As individual visuals are hyperlinked to reports and completely interactive, you just need to resize them on your dashboard and they show up perfect.

Tip: Use the focus mode for a closer look. In this mode, the visual will take up the entire windows for improved insight.

You can also design a dashboard for mobile by looking at the phone-view version. Power BI will put visuals on the mobile dashboard automatically, so you just need to resize them slightly. Use small multiples on your mobile dashboard and view them in a single column.

The power of responsive visuals

Having custom visuals that are truly responsive is a great asset when you design Power BI dashboards for mobile. Truly responsive Power BI custom visuals can do this:

When there's enough space for just one chart, only one chart is displayed. When there's space for two charts, two charts are displayed. And when space is plentiful, three charts are displayed.

Tell a story with your Power BI Dashboards

Power BI is a great storytelling tool. However, to really take advantage of everything it has to offer, we recommend you use the desktop version. In addition to powerful modelling tools, it provides an easier way to hyperlink individual reports or even visuals.

Start by adding shapes or text that can be linked to individual visuals. You can even link to visuals in spotlight or focus mode, so that report readers are guided to the information they need. You can therefore easily create a visual story through bookmarks, as reader just needs to click on arrows at the bottom of the screen to move through the bookmarked visuals.

Try it yourself

The above Power BI dashboards and reports were created using Zebra BI custom visuals. They are designed to help you build extremely clear, detailed and insightful business reports and dashboards in Power BI.

A special attention and a great deal of effort have been invested into making the process of building even the most complex business dashboards a straightforward, extremely fast and enjoyable experience.

You can download Zebra BI custom visuals for Power BI here:

Try Zebra BI custom visuals