More and more things are being automated for faster delivery, which is also valid with Power BI. In this article, we look at how to automate the creation of a new Power BI report based on the dataset.
When working with Power BI daily, sometimes report developers to miss the opportunity to utilize a newly available function that can help out when a “quick” report needs to be developed. In the past, you would have to install and fire up Power BI Desktop and go through a significant design process. The process would include establishing data sources and transformation models and determining the best visuals placed on the design grid. Once the design was finished, that Power BI file would need to be published to the Power BI Service on the web. Such a process would be a fair number of steps to achieve a quick report. However, with a recent upgrade to Power BI, this whole process can be curtailed to a simplified process to establish a quick dashboard with visuals related to a dataset.
Usually, in this section of the tip, a description of installing Power BI Desktop would be provided, but installs are not needed for this particular tip. However, it would be recommended that installing Power BI Desktop would be advantageous for any dashboard developer. You can download the latest version of Power BI desktop to follow along with the examples.
We will actually utilize the Power BI Service to complete the tasks described in this tip for this tip. Thus, you will need access to the Power BI Service through either a work or school account.
From the main Power BI home page, two options are available for creating a new report online, via the Power BI Service. Either the “+” plus sign Create button on the left menu can be selected or the “+ New report” button can be selected in the upper right menu.
Both get you to the same place, which is the Add data to get started (Preview) screen shown below.
Currently only a few options are available to get your data into the report:
You can copy and paste your data set into the entry screen
You can manually enter the data into the entry screen
You can select a currently published dataset
However, only the manual entry or cut and paste method allow for the dashboard report’s auto-generation. Using the “select currently published dataset” will generate a blank report.
Thus, using the entered data manually option opens a data entry grid that allows copying and pasting or simply manually entering the data. Notice the three options available on the design grid. The top dropdown box allows for defining the first row as the header for the table.
For the rows and columns, click on the + buttons as either rows or columns, respectively. Furthermore, the column name can be changed by double-clicking on the column name.
Likewise, the column data type can be adjusted by clicking on the ABC123 button on the left side of each column header. This change can be very helpful as it keeps the data consistent while also preventing certain typing and conversion of data types errors.
In any case, navigating the grid is similar to Microsoft Excel. For example, using the enter key moves the current cell down one cell. Similarly, the data entry can move side to side using the arrow keys to move left and right.
One final note about the design grid, be sure to name your table something appropriate and contextually sound and logical; this is the name of the dataset that users see.
Be sure not to have a cell selected; if you do, the copy/paste will put all the data in a single cell.
A second issue that you can run into pertains to the data types. Power BI allows the dashboard designer to change the data type to something that does not match the actual data in the column(s).
As shown below, the product field allowed the selection of a whole number for the Product column, which contains alpha characters.
Only upon clicking the Auto-create report button does an error appear. One impressive feature of the error message is that it provides row numbers where the errors could be occurring. That can be helpful if the number of problem rows is minimal (one or two for instance) in an extensive data set.
Three options are available once the data set has been copied in or manually entered to create the report. We are focusing on the Auto-create option. The create a blank report option is similar to the design experience in Power BI desktop, while the generate a data set only option will add the dataset to available datasets on the service.
Clicking on the Auto-create report creates and opens the newly designed with a set of suggested visuals. In the screen print below, you can see that Power BI created 8 visuals, six-column charts, and 2 cards in the upper right corner. Notice in the bottom right corner that the Preview status is highlighted. Power BI makes certain assumptions about the data to provide the initial list of charts.
The robust feature here is that the newly designed report allows for other charts and will automatically make the changes. For instance, if Discount Band is selected from the field list, a new set of charts is added on the right side with the sum of units sold and sum of COGS all by Discount Band. In this case, Power BI knows that Discount Band would be a category field.
Similarly, unchecking a field will remove the related charts to that field. In the illustration below, the country was removed.
In the above, examples, categories were added or removed. New measures can also be selected which would add a whole new row of charts. Thus, using the below example, the Gross Sales measure is selected, and a new set of 3 charts based on Gross Sales is added, plus a new card at the top of the dashboard is added.
However, if you attempt to add a 4th measure, the error shown in the following screen print results. Power BI currently only allows 3 measures (rows) and 4 categories (columns). Trying to add fields beyond those numbers will result in an error.
Additionally, you will notice on these screens you have limited editing capabilities including not being able to make formatting change or changes to the field lists on a particular visual. Filters and sorting can be applied to the visuals, though.
When you are satisfied with the report, clicking on the save button save the report and the report will automatically be saved to your workspace area on the Power BI Service.
Even with the report saved, the interchanging of categories and measures still can be made by dashboard designers AND dashboard viewers and these changes can be saved by report designers. This dashboard is considered to be using the summarize pane.
Also, at this point, the report can be edited similar to any other report. However, you will receive the below warning. The warning is basically telling you that once you switch to edit mode, the Auto-created Summarize pane options will no longer be in effect and each visual must be modified on its own (like you would do in other reports).
In this tip, we reviewed the process of allowing Power BI to Auto-Create a report for you based on data input or copy into a new dataset. Currently, this method is the only supported option for entering data, but other options are expected in the future for this preview feature.
This Function returns a related value from another table
This Function evaluate a table expression in a context modified by the given filter
This Function specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.
Power Bi Relationship Function
Power Bi Relationship Function Example
Here we will discuss different types of relationship functions with the examples in Power Bi.
So here we will use the US Superstore dataset. And then in Power Bi, I build a data model using Power Query.
We created a relationship between the tables like below:
Example 1: Using Power Bi CROSSFILTER Function
Here we will see how to use the CROSSFILTER function in Power Bi.
The Power Bi CROSFILTER function is used to implement a specific cross-filtering behavior in a calculation where a relationship exists between two columns.
So, here we will calculate the total sales and distinct product count for each year by using measures.
First, we create a measure that will calculate the total sales in the sales fact table.
Total Sales = SUM('Fact-Sales'[Sales])
Then we will create a measure that will distinct count the product from the dim product table.
Distinct count on product = DISTINCTCOUNT('Dim-Product'[Product ID])
Now create a table visual, dim order table adds the year from the date hierarchy. Then add total sales and Distinct count on product measure in the table visual.
Now, in the above report, you can see the product count is repeated for each year. That means, even the relationship is mentioned between sales fact and product dimension, the filter is not applied as per the requirement.
So if we change the relationship between these two tables as both cross filter directions, then all other measures will be impacted.
So we will use the cross-filter direction relationship only for this measure. Let’s create a measure using CROSSFILTER Function to Distinct Product Count.
Product count on Crossfilter = CALCULATE(DISTINCTCOUNT('Dim-Product'[Product ID]), CROSSFILTER('Fact-Sales'[Product ID],'Dim-Product'[Product ID],Both))
Example 2: Using Power Bi RELATED Function
Here we will see how to use the RELATED Function in Power Bi.
The Power Bi RELATED Function returns a column from another related table
Key Points of the Related Function:
Requires a relationship between the current table and the related table.
This function follows many to one relationship. And without a relationship, it will not work.
It will work only in a row context.
So here we will compare the yearly sales for all 4 regions as well as for the non-central regions.
We will create the two measures, one will calculate the total sales, and in another, we will calculate the sales for the non-central region.
So total sales we have already calculated in Example 1.
So, let’s create a measure in which we will calculate the Non-central region total sales.
Noncentral region Total sales = SUMX(FILTER('Fact-Sales'
Example 3: Using Power Bi RELATEDTABLE Function
Here we will see how to use the RELATEDTABLE Function in Power Bi.
The Power Bi RELATEDTABLE Function returns a table of values. It considered an existing table as a parameter.
Key Points of the RELATABLE Function
RELATABLE Function is a shortcut function for the calculate table function with no logical expression.
This function follows one too many relationships.
So, here we will calculate how many times one product has been used in the sales table, which means we will do the product counts for the sales table.
For this we will create a calculated column in the product table, then we will find out the count of products in the sales table. CLick on Modelling tab -> New column.
Product count from sales table = COUNTROWS(RELATEDTABLE('Fact-Sales'))
Example 4: Using Power Bi USERRELATIONSHIP Function
Here we will see how to use the USERRELATIONSHIP Function in Power Bi.
The Power Bi USERRELATIONSHIP does not return anything but it specifies between two columns to define the relationship for calculations.
And also it uses the other functions as an argument like CALCULATE and CALCULATETABLE etc.
So, in our current data model, we have two date tables i.e. Dim Order Date and Dim Ship Date. And both the date table has one active join with a sales table using order date.
Now the question is how the ship date can be used if we want to calculate the total sales based on the ship date.
For this, we will create a new Date table, then we will add an active relationship between the order date in the sales table and the date column of the date table.
In Power Bi Desktop, Click on the Modelling Tab->New table, then write the below DAX formula to create Dim Date:
Dim Date = CALENDARAUTO()
Then we will create two relationships one is the active relationship i.e. order date column in Fact sales with the Date column in the new date table.
And the other relationship is the non-active relationship i.e. ship date column in Fact sales with the Date column in the new date table.
Non Active relationship
Now we will calculate the total order sales by using the below measure:
Total order sales = SUM('Fact-Sales'[Sales])
Then we will calculate the total sipped sales using the below measure:
Total Ship sales = CALCULATE(SUM('Fact-Sales'[Sales]),USERELATIONSHIP('Fact-Sales'[Ship Date],'Dim Date'[Date]))
https://nexumbs.com/wp-content/uploads/2021/10/Power-Bi-relationship-1024x594-1.png5941024Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-10-21 09:42:232021-10-24 10:05:51Power Bi Relationship Functions
As a business analyst, you need more than just basic knowledge of building financial reports, whether in Power BI or another software.
Below you’ll learn about the best practices that the world’s top companies are successfully adopting.
You will learn the tricks to create attractive and insightful financial reports. We will look at Sales vs. plan performance dashboards, cost vs. budget analyses, income statements, and forecasts.
The 4 questions every report should answer.
You’re probably often dealing with similar datasets like the one below. This is a typical sales dashboard with a dataset that includes different dimensions and data fields. For example, you can see things like the breakdown of sales by geography and by time, indicating there’s a calendar behind in this model. Product categories, product sub-categories, products, customers, customer segments, and other elements.
So, it’s a typical sales dashboard above, but does it really get the message across? Is it understandable? Is it actionable? Can you answer certain basic questions about the sales performance? For example, the first very trivial question, is my sales performance good or bad?
It’s hard to say, despite having all the breakdowns by geography, product categories, and more. A chart like this makes it essentially impossible to answer a straightforward business question. Why? The key problem here is that there’s no comparison to the previous year or planning. There is no context of the sales here. So, if you cannot observe the variances, if you cannot really compare the categories in your data, and if you can’t do this in a simple visual way, then your dashboards will fail.
Second question: How good or how bad? Are my sales on target? Above target? Again, I don’t know.
Third, why is the sales performance as it is? Difficult to tell.
And the final, perhaps the most important question, “What are we going to do about it?” Unfortunately, this dashboard contains no actionable information that will tell you that you’re 30 percent below the plan, the reasons, and where you should focus your attention on that, and this is what you should do about it.
So, these are the key four questions that are like a litmus test for your dashboards and reports. Your reports are understandable and actionable when you can answer these four questions fast for any report or dashboard page.
The 4 questions you should ask yourself for any dashboard or report page:
Is my performance good or bad?
How good / how bad?
Why is my performance as it is?
What are we going to do about it?
The business analyst’s toolbox
To answer these 4 questions, you need to equip yourself with proper tools that every business analyst should add to the business analyst’s toolbox. These tools include, in no particular order:
1. Advanced charts
Power BI has a lot of visuals. It has everything from pie charts to treemaps, map visualizations, clustered column charts, etc. However, it does not offer advanced business charts – charts where you can compare actual to plan or actual to forecast or actual to the previous year, all of which are a basic requirement in any business analysis.
2. Variance analysis
Most of the advanced charts will enable you to perform variance analysis. Clear and understandable variance analysis is probably one-half of every business dashboard or report you’re creating.
3. Top N + Others
You may also need more advanced filtering. You can filter, for example, the top five elements by value in Power BI, but you can’t really do things like Top N + Others. This feature allows you to set the number of top elements you want to display along with the element called “Others“, which will combine all remaining elements into one. This is an important little tool you should deploy in your dashboards, but it’s tough to do in Power BI. It takes some more advanced DAX that you have to build into your Power BI model to deliver that.
4. Bridge charts with Top N
We’ll talk about waterfall charts, especially bridge charts with Top N, where you’re not just shoving all of your data at once. Instead, you can actually ensure you’re only rendering the most important changes, the most important elements that contributed either positively or negatively to your total result. A good bridge chart with Top N elements, either positive or negative, is a great business analysis tool in Power BI dashboards and reports.
5. Grouped tables with variances
A serious business analyst needs simple basic tables or metrics in Power BI, but clear tables with variances and several groups. For example, first, you should present monthly results or month-to-date results for a particular period, then the next group for year-to-dateresults, and another group at the end, where you’re presenting full-year results. In the full-year results, you should also present forecast vs. plan, whereas in the current period, you’re probably presenting actuals versus plan.
6. Bridge charts with subtotals
This is especially useful when creating an intermediate result within your chart before the final result. This chart helps you show the difference and explain how a certain value changes and what the causes are for that specific change. This is particularly useful with EBIT and EBITDA. To do this, you need waterfall charts with subtotals and a very flexible way to present and calculate them.
7. Displaying forecasts
Too often in Power BI and other tools, the business performance analysis focuses on data already in the past. But we forget that in reporting, we should also look into the future and determine what is going to happen? That’s why it’s wise that you bring your forecast estimations, latest estimates into Power BI and to present them very clearly in your reports and dashboards.
8. Income statement / P&L / Price calculations
If you’re presenting income statements, P&L’s or similar calculations, then you need additional tools like vertical waterfall charts that we call calculation charts. These can be quite challenging because P&L’s and income statements have mixed datasets consisting of positive KPIs, such as revenue, and negative KPIs, like costs, expenses, that need to be subtracted. This is complicated on its own but can get very complex very quickly if you decide to add things like subtotals.
Variance analysis is one of the most fundamental and decisive tasks of every business analyst. PVM variance analysis allows you to calculate how your product price volume and mix changes affect your revenues. You can use this analysis for individual segments or categories within your company.
As a business analyst, you are always challenged by how much data and visuals you should insert into a report. Add too much, and the end-user won’t be able to make head or tail out of it. Add too little, and you might miss crucial information to act upon. So how do you strike that balance?
One method to both present more information and improve insight is called small multiples. This allows you to display more charts within a single visual and increase information density.
In this article, we presume that you, as a business analyst, already have some basic understanding of Power BI. But if you are just getting started or would like to become more proficient with Power BI first, you can check out our knowledge base that contains informative articles, videos, and examples for Power BI.
To better showcase the impact of each tool, we’ll be working with Zebra BI visuals. Most, if not all, of these examples can be recreated with native Power BI visuals and you can download a free version of Zebra BI Charts and Tables from App source or directly from Power BI.
We will start with a combination of tables and charts showing sales by business units.
Variances are hard to do in Power BI
A basic example of variance analysis would be comparing Actual to Plan values. The easiest solution would be simply placing the columns of those values next to each other and calling it a day. The problem with those basic side-by-side charts is that you are not explicitly visualizing the gaps. While it’s important to show individual values, so the end-users understand which is the largest business unit and how others compare, you must also display the gap between values. Only then can you answer basic questions, such as whether you are achieving your plan. That is really hard to do if you just have two different colored bars standing next to each other.
If you switch the chart to Zebra BI Tables, it automatically calculates and creates variances for you. Even more, the visual response to the space available and adapts the information displayed accordingly.
The great thing about having your variances shown is that you can sort by them to identify the best and worst-performing business groups quickly.
Another thing you would typically do in a table is to build a hierarchy by adding multiple fields into a Category. The users will be able to expand and collapse the tables to get to the details. This allows you to group your results into different subgroups. For example, you might have two groups – Personal care and Electronics, each with individual divisions, such as Mobile, Video, and Computers. The Mobile group consists of tablets and smartphones.
Tip: expose the first two levels in the initial view by default. From here, the user can simply click to drill-down to get into the details.
The next thing in a typical table-based report would be creating multiple comparisons like comparing the Previous year alongside the comparison to Plan. You then get more columns that you can arrange according to your preferred pattern, like first comparing the Previous year before the Actual value, followed by comparison to Plan. Or you can place absolute values, followed by absolute variances and relative variances. In the end, the main goal is to make it as clear as possible and for it to fit your column-structure standard.
Designing Zebra BI variance tables
Now the only thing left is to polish the design of your tables. In Zebra BI, you can switch the visualization in each column, such as changing the format from bars into a simple value column with numbers. Sometimes you can even hide certain columns if you don’t need all the measures in your tables. Simply click on the column heading and select the Hide column option. This way, you keep the data within the table; you just decide not to show it.
With tables, they must be readable, so you need to make sure you have enough space for the charts and other data. So one of the many things in Zebra BI that will help you control this experience is responsive visualization. It means that depending on the visual size, the program will adapt the number of values shown and how they are displayed.
You can adjust the Minimum chart width value in the Chart settings section to determine when the data is still being rendered before it collapses or changes format to better adapt to the new size. Just be careful not to make it too small, or the charts will look too squeezed together. Like with everything, you need to strike the right balance.
Further customizing your tables.
Another feature to further customize your tables is the Group functionality. For example, you can use it to show both year-to-date and monthly results in the same visual. In our example, we insert our Period calculation field into the Group placeholder. The period calculation is the data field that contains all the period options. This is much faster than having many separate visuals to present fresh information since Power BI now only triggers one query.
Similarly, instead of using month versus year-to-date values in your tables, you could just insert certain elements from your calendar dimension for a different view of your data.
If you have well-prepared calendar information, then you can, for example, take your Quarters field and insert it into the Group placeholder to analyze your data by quarters. A word of caution, though: if you put too many elements in your columns, you will have too much information. It is important to keep things clean and not waste unnecessary space with unnecessary information.
A good way to save space is to ensure a clean display of data labels. For example, most of your values could be in thousands, so zeros or the letter K are displayed across your table. Instead, just use Zebra BI to display the letter K in the title. The result? The entire visual is more legible and easier on the eyes. The columns are narrower, and you can present more information. In business reporting, you will often present data in thousands, millions, billions, etc. So always keep the labels in mind.
This setting is in the Data labels pane in the “Show units in” field.
Totals and Subtotals
With Zebra BI, you can show the Grand Total of the full year with just one click. You can do that with the + icon, where you can add additional columns.
The feature is fully customizable. You can change the names of columns directly inside the visual or hide the columns you don’t need. When hiding columns, you have granular control over hiding them in a single group or across all groups (see image below). To unhide it, click on the + icon again, and a list of all hidden columns will appear, along with the one you seek. Next, click on the column’s name, and it will appear once again within your table.
The next stage would be also to build a hierarchy in your groups. Again, this is possible in Power BI because of the multilevel grouping. For example, if you add the month data under the Group field’s quarters, you’ll also show months aggregated to the quarters.
To do this, just add Quarter and Month measures to your Group placeholder.
You can then collapse these quarters and only show the last one that concerns you the most.
Whenever you have a hierarchy of at least two levels in the Zebra BI table, you can also show the subtotal. Here, the subtotal is just the result for the one quarter, which is then displayed along the months.
Show the variance versus plan as a percent of the plan
What if you want to show relative variance as a percentage of the plan instead of the increase/decrease in the plan? For example, instead of showing the value to be -9% below the plan, you present it as 91% of the plan instead. Some specific cases require you to use such notation, especially in project management. While there’s no specific function for that, you can add your own custom index. You calculate your index and then just put it into the Values placeholder in Power BI, and it will appear as an additional measure. This is what we call custom-calculated columns. It will appear as a distinct measure.
Below is a typical example of an income statement with the previous year, actuals, and variances. But to finish the report, we would also like to add the percentage of revenue, which you can calculate using DAX. You can download our PBIX file and try it for yourself.
Once you calculate it (already included in our sample file), take this new measure and place it in the Values placeholder, below the AC value. Now it just acts like any other column, which you can then rename and move around. You can also further format each column by giving it a border or a background color. In the end, the P&L report should look something like this:
Changing views in Tables
Zebra BI tables allow you to design two views. The first view is what we call the more visual chart view. The second one is called table view that provides more numbers and other data. In this way, end users can first focus their attention on the most important information, like the variances, and then read the full tables and numbers they need.
This is a great compromise; show the visual design first, with fewer columns, but still access all values with one click. This easy-to-access information change by chart slider is one of the best practices you can apply to your own reports.
Variance analysis in charts
Let’s talk about charts and the various analyses you can do with your visuals. Compared with tables, charts are more often used when you break down by month or work with a time component. Time should run left to right, so use charts with a horizontal axis whenever you have a time series.
When it comes to presenting single measures, native Power BI does its job well enough. Typical examples include market share, HR reports, and simple dashboards presenting data within a certain time frame. But even then, you can do something like show the comparison to the first point in the chart. Zebra BI Charts, in this case, offer you multiple options to showcase your data and focus on what is most important.
But where native Power BI falls behind is with multiple measures. A typical example is a comparison between Actuals and the previous year. By default, Power BI neither visualizes the gap between the measures nor adds any additional labels. You would need to fiddle around with the settings, but even if you managed to get the right labels and other elements in place, this will still be just a normal side-by-side chart.
Variance analysis with waterfall charts
Below is a typical example made with Zebra BI Charts. It’s the basic waterfall (bridge) chart, where you have the totals for the previous year and for actual. On the right is the growth rate compared to the previous year, which is already calculated. All the elements are completely interactive, so if you click on the growth rate label, you can choose to show absolute variance, relative variance, or both.
You can also cut the axis to zoom into the variances if you want people to just focus on the variance. Additionally, you can choose in the settings how much you break the axis.
What does that mean? The percentage shows how much of the total value you are showing. So if you set it 50%, you’ll only show the upper half with the variances, while everything else will be below the chart.
Tip: if you want to show just the variances, cut the axis to 100%.
Available types of variance charts
Zebra BI offers several useful charts in addition to the waterfall chart, including a bar chart with variances, area charts, and line charts.
The great thing about these visuals is that they have smart responsiveness built-in. Take the bar visual, which starts with the bars for values and two additional charts for absolute and relative variances. If you shrink it, separate charts for variances are removed and integrated into bar charts. This is what we call integrated variance charts. Of course, when you resize them back up, additional charts appear again.
However, you can always set the chart responsiveness manually. For example, you might not want the responsive chart in your dashboard, so just open Chart settings and set the Layout from Responsive to another option, such as Integrated variance or Absolute Variance or similar.
Adding another dimension (small multiples)
Now that we have a great-looking chart with variances between actual and previous year’s results, we want to analyze data by groups. With default charts in Power BI, that’s almost impossible to do elegantly. Instead, you have to fiddle around with drill-downs and barely intelligible charts that tell you very little.
In Zebra BI, you can use scaled small multiples. Small multiples is a visualization method that puts all charts of different categories on a single page and scales them so that you can easily see which ones are the most important and analyze the trends.
You can easily see that the Skincare business unit is the least important. If you want to review it, you simply click that part of the visual, and it opens up as a full chart that’s easier to analyze. It’s a great way to first look at the forest and then look at every individual tree.
When working with small multiples, you have to clean things up a little so they are easier to read. This usually means adjusting the Data labels density. For example, you have the setting First, last, min, max, which only displays the first and last values and the largest and smallest value in between.
Another important feature is the Top N setting. This shows only the categories that have the most impact on your business. In the Visualizations pane, select the small multiples category and enable the Top N feature. Instead of showing 20 business units, it can only display the top 8. But instead of just cutting off the other categories, it bundles them into the Other category, which is the sum of all other categories.
Zebra BI gives you plenty of control over small multiples. You can set grids, change axis labels, colors. However, we recommend that you set the Layout setting to Auto because it intelligently sets up your small multiples to make the best use of space.
Looking great on mobile
Small multiples also look great on mobile. Zebra BI will automatically adjust them, so you only need to open the Mobile layout on your View tab and drag and drop a Zebra BI visual onto your canvas.
You can even change Zebra BI layouts on mobile.
Advanced features for business analysts
When it comes to business analysis, waterfall charts are fundamental, and sometimes it’s nice to have the ability to show subtotals in the chart. For example, when currency fluctuations impact your EBIT, you might want a waterfall chart that shows your EBIT before the impact of currency fluctuations.
You need to create a table with all of your results already calculated in a simple flat table to achieve this. Next, click your chart and set your EBIT elements as results. To do this, right-click on them and mark them as Results. You can then mark any of your elements as results and have complex calculations in your chart.
Waterfall charts can also be used for Price-Volume-Mix analyses, where you can quickly see how price, volume, or product mix affect your sales.
Subtotals in tables and hierarchy
You can do similar things in Zebra BI tables, such as Profit and loss statements and income statements. First, let’s look at an income statement, where Revenue is the first subtotal of product and service revenue.
This is very easy to do. Just right-click the name and select Result entry from the menu.
When a certain value is a cost, you simply select Invert to show it has a negative contribution to the final result (or subtotal). This is called row-level invert, giving you complete control over individual entries in your dataset.
Forecasts come in handy when you want to look at data for the full year before you actually have that data. Instead of comparing actuals to your plan, you need to compare the forecast to plan.
The trick is to add all three values to the visual: Actuals, Plan, and Forecasts, which you should drop into their respective placeholders. Then you simply click on your visual and hide Actuals from the Full-year group of data.
You can also add forecasts to your charts. This will allow you to view actual results up to the current month and then forecasts onward. Like this:
For this to work, fields with actual results in your data need to be empty. Then, zebra BI will automatically know what to do with it.
These are the tools you need
If you are a business analyst, this post will surely help you move beyond confusing charts and into a world of intelligible charts that tell a story.
Instead of combing through data, you can use these tips to create reports that pinpoint trends, identify weaknesses, and show the strengths.
While Power BI is a powerful tool, it is more powerful with Zebra BI.
With Zebra BI, you need minimal effort to implement advanced waterfall charts, small multiples, and income statements that make sense. Some of these things are much harder or even impossible to do in vanilla Power BI.
https://nexumbs.com/wp-content/uploads/2021/09/Power-Bi-Sales-Dashboard.png8461500Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-09-26 22:19:392021-09-25 22:43:16The best tools you need in your toolbox for Power BI
A very common requirement for a Power BI report that I stumble across at almost all of my customers is to automatically show data for the current day/month/year when a report is opened. At first sight, this seems like a very trivial problem but once you dig into the problem, you will realize that all of the common solutions out there have some disadvantages and only solve the problem partially.
So here is what we want to achieve:
Show the Current Month (or Day, or Year) by default
Works [in combination] with all other columns in the date table.
A single, easy to use slicer/filter to control the time selection and change from Current Month/Day/Year to any other value
Works with built-in time intelligence functions
Works with existing DAX measures
Works with any datamodel/report
Solutions like Relative Time Filter/Slicer, DAX or relative flags in the date table address only some points of the above list but definitely not all of them which is why I thought we need a better solution to this:
We actually created a new table in our data model that is linked to the original date table. The reason why we cannot use the same table here is that the new table does not have unique date values as all dates/rows referring to our current calculations are duplicated. It has to be a many-to-one relationship with cross-filter direction set to both (even though we will only use the new table ‘Calendar_with_current’ to filter the existing table ‘Calendar’):
And that’s it basically. You can now exchange the original Calendar table with the new one to get the new “Current” values in your report. If you have time intelligence functions in place, you further need to extend them and add ALL('Calendar_with_current ') them as a filter to make them work also with the new table. The old table can also be hidden now if you do not want to confuse the end-users. To make a seamless switch you can further rename the tables.
I added an additional column to the table called Type that allows you to select which values you want to show – the original values (e.g. “September”), the values with “Current X” (e.g. “Current Month”), or both. Please see the second page/tab of the embedded report above.
So this raises the question of how this new table can be created? To simplify this I have created a Power Query function that takes 3 parameters:
The current date table
A list of definitions of your current-values
The name of the unique date-column in your current date table (parameter 1)
The first and the third parameter should be clear, but what is the “CurrentDefinitions”?
It is basically a table that defines the relative time calculations that you want to extend your existing date table with. Here is an example:
The column Column refers to the column in which you want to create the relative date definition. The column NewValue specifies the value that you want to set for rows that match the third column Filter. The column Filter either takes a static filter expression like [RelativeMonth] = 1 (as in lines 5-8) but can also use existing M-functions and reference the existing Date-column using the placeholder <<DateColumn>> as you can see in lines 1-4.
The table can be maintained using “Enter Data” and can contain any number of rows/definitions!
For most of my scenarios, this works pretty well and addresses all major problems highlighted above.
https://nexumbs.com/wp-content/uploads/2021/09/relative-date-range-slicer-filter-01.png4421128Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-09-05 15:29:192021-09-04 15:56:58Doing relative-time Slicers properly in Power BI
Power BI offers different kinds of samples, for different purposes:
A Power BI report (.pbix file) that you can view in the Data Stories Gallery, open and explore in Power BI Desktop, or upload to the Power BI service.
A sample app that you download from AppSource, also right in the Power BI service. Apps include dashboards, reports, and datasets. You can modify them, then distribute them to your colleagues.
Eight original built-in samples in the Power BI service, with dashboards, reports, and datasets. You install them right in the Power BI service. The built-in samples are also available as Power BI reports (.pbix) and Excel workbooks (.xlsx).
Excel workbook versions of the built-in samples, containing the data model and Power View sheets. You can explore or edit the data model in Excel, use the Excel workbook as a data source for a Power BI report. You can also upload the workbook as an Excel file and display the Excel visuals and PivotTables in Power BI reports.
Also a Financial data sample workbook, a simple flat table in an Excel file are available for download. It contains anonymized data with fictitious products, with sales divided by segments and countries/regions. It makes a useful basic data source for a Power BI report.
Our online documentation uses these same samples in tutorials and examples, so you can follow along.
Sales & Returns sample .pbix file
The Sales & Returns sample report
Power BI report designers Miguel Myers and Chris Hamill created the Sales & Returns .pbix file to demonstrate many new features in Power BI, including buttons, drillthrough, conditional formatting, what-if, and customized tooltips.
The scenario for this report is a company that sells Microsoft-themed skateboards. They want to see the state of their sales and returns, and analyze how they should modify their operations.
The Marketing and Sales app is available from Microsoft AppSource. An app is a Power BI content type that combines related dashboards and reports. An app can have one or more dashboards and one or more reports, all bundled together. You can download the Marketing and Sales app from Apps in the Power BI service, or by going to AppSource in your browser.
The company obviEnce (www.obvience.com) and Microsoft teamed up to create samples for you to use with Power BI. The data is anonymized and represents different industries: finance, HR, sales, and more.
Each of these samples is available in several formats: as a built-in sample, as an Excel workbook, and as a Power BI .pbix file. If you don’t know what these things are, or how to get your hands on them — don’t worry. This article explains it all. For each of these samples, we’ve created a tour. Tours are articles that tell the story behind the sample and walk you through different scenarios. One scenario might be answering questions for your manager, another might be looking for competitive insights, or creating reports and dashboards to share, or explaining a business shift.
Before we get started, here are the legal guidelines for using these samples. After that, we introduce the samples and show how to use them.
ObviEnce is an ISV and an Intellectual Property (IP) Incubator focused on Microsoft Business Intelligence. ObviEnce works closely with Microsoft to develop best practices and thought leadership for jump-starting and deploying Microsoft Business Intelligence solutions.
The workbooks and data are property of obviEnce, LLC, and have been shared solely for the purpose of demonstrating Power BI functionality with industry sample data.
This industry sample focuses on the hiring strategy for a company by analyzing new hires, active employees, and employees who have left. By exploring the data, you can find trends in voluntary separations and biases in the hiring strategy.
In this industry sample, we analyze the planned vs. actual costs of the IT department of a company. This comparison helps us understand how well the company planned for the year and investigate areas with huge deviations from the plan. The company in this example goes through a yearly planning cycle, and then quarterly it produces a new Latest Estimate (LE) to help analyze changes in IT spend over the fiscal year.
This industry sample explores a software company’s sales channel. Sales managers monitor their direct and partner sales channels by tracking opportunities and revenue by region, deal size, and channel.
This industry sample analyzes retail sales data of items sold across multiple stores and districts. The metrics compare this year’s performance to last year’s in these areas: sales, units, gross margin, and variance, as well as new store analysis.
This industry sample analyzes a manufacturing company, VanArsdel Ltd. It allows the Chief Marketing Officer to watch the industry and the market share for VanArsdel. By exploring the sample, you can find the company’s market share, product volume, sales, and sentiment.
This industry sample focuses on one of the typical supply chain challenges — supplier quality analysis. Two primary metrics are at play in this analysis: total number of defects and the total downtime that these defects caused. This sample has two main objectives: understand who the best and worst suppliers are, with respect to quality, and identify which plants do a better job finding and rejecting defects, to minimize downtime.
Install built-in samples
Let’s start with the built-in samples. The built-in samples are available in the Power BI service; you don’t have to leave Power BI to find them. These samples are each a bundle of one or more dashboards, datasets, and reports that someone creates and that can be used with the Power BI service. These built-in samples are still available, but are being deprecated. They aren’t available for Power BI Desktop.
Open the Power BI service (app.powerbi.com) and sign in.
Navigate to your My Workspace, or another workspace where you want to install the sample.
In the bottom-left corner, select Get data.
On the Get Data page that appears, select Samples.
Select one of the samples to open a description of that sample, and choose Connect.
Power BI imports the built-in sample and adds a new dashboard, report, and dataset to your current workspace. Use the samples to take Power BI for a test run.
Now that you have data, you’re on your way. Try out some of our tutorials using the built-in samples or just open the Power BI service and explore.
Download original sample Power BI files
Each of the built-in samples is also available as Power BI .pbix file. The .pbix files are designed to be used with Power BI Desktop.
Download the files individually using the links below. Selecting these links saves the file automatically to your Downloads folder.
Save the downloaded file. Where you save the file makes a difference.
Local – If you save your file to a local drive on your computer or another location in your organization, from Power BI, you can import your file into Power BI. Your file remains on your local drive, so the whole file isn’t imported into Power BI. What happens is a new dataset is created in your Power BI site and data, and in some cases, the data model, is loaded into the dataset. If your file has any reports, those reports appear on your Power BI site under Reports.
OneDrive – Business – If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, OneDrive for Business is by far the best place to keep your work in Excel, Power BI, or a .CSV file in-sync with your dataset, reports, and dashboards in Power BI. Because both Power BI and OneDrive are in the cloud, Power BI connects to your file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI.
SharePoint Team-Sites Saving your Power BI files to SharePoint – Team Sites is much the same as saving to OneDrive for Business. The biggest difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.
Open the Power BI service (app.powerbi.com) and sign in.
Navigate to your My Workspace or to another workspace, or create a workspace just for the sample.
In the bottom-left corner of the navigation pane, select Get data.
On the Get Data page that appears, select Files > Get.
Select the location where you downloaded and saved the sample.
Select the file. Depending on where you saved the file, select Connect or Open.
Choose whether to import the data or to bring the workbook into Power BI and see it exactly as it is in Excel online.
If you select Import, Power BI imports the sample workbook and adds it as a new dashboard, report, and dataset, in this case, each named Procurement Analysis Sample.
Because the workbook has Power View sheets, Power BI creates a report with a page for each Power BI sheet.
Power BI creates a new dashboard with a new blank tile. Selecting that tile takes you to the report you just added.
Open the report. Select different elements of the report to explore their interactions.
Explore Excel samples inside Excel
(Optional) Want to understand how the data in an Excel workbook gets converted to Power BI datasets and reports? Opening the Excel samples in Excel and exploring the worksheets provides some of the answers.
When you first open a sample workbook in Excel, you may see two warnings. The first warning says the workbook is in Protected View. Select Enable Editing. The second warning may say that the workbook has external data connections. Select Enable Content.
Each workbook contains several Power View sheets. If you want to see the Power View sheets in Excel, You need to enable the Power View add-in by downloading a package of registry keys.
So where’s the actual data? It’s in the Power Pivot data model. You don’t need the Power View sheets to see the data. On the PowerPivot tab, select Manage.
https://nexumbs.com/wp-content/uploads/2021/09/sales-returns-sample-pbix.png6511002Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-09-04 09:22:572021-09-04 09:22:57Get samples for Power BI
To best understand how the small multiple works, let’s go through an example. Suppose that we have the visual below;
It shows the Sales by year, and obviously, you can see that the sales are growing from 2005 to 2007, but the sales value in 2007 and 2008 is almost the same. In the visual above, there is no mention of what type of customer. I’d like to know if the type of customers, or more specifically their education category, derives a different behavior through the years or not. In other words, I want to see how the sales pattern is through the years, but for all education categories. We have five education categories in the customer’s table.
If I add that as a legend to this chart, it becomes very complicated like below;
The visual above works fine, but it is not easy to understand, so many columns/bars and values. A golden rule to have a good visualization is to keep it simple but informative. This is not simple.
One other approach is creating five copies of the same visual and filtering each for one of the education categories.
This method works, and it is much simpler to understand than the prior method. However, there is a high cost of maintaining this report. Every time I want to change something in the visual, I have to do it for all the five charts! This is not a maintainable solution.
This means creating a separate title or label for each visual, setting a separate filter on each visual, and much more settings. The maintenance cost of this second method is normally a blocker for many developers. They go back to using the first method, which means not having a good visualization and report for their users to understand.
Small multiple is an answer to the requirement above. Small multiples will create copies of that chart by every value of the education column, but they are still part of one visual. This means if you change a property of the visual, it changes for all the copies (or, let’s say, multiples). And it is easy to understand at the same time.
Let’s see how small multiples work. All you need to do is add the education field to the small multiples field of the visual (this is only available for the column chart, bar chart, line chart, area chart, and column/line combo charts).
This will immediately create multiples for each value in the education column. In this case, five multiples of the same chart are created.
Small multiples create copies for you, but as part of the same visual. It multiplies the visual for you.
As you can see in the screenshot above, the multiples are spread in a grid of two columns and multiple rows. You can adjust it if you want. This configuration is in the Format setting of the visual under the Grid layout.
You can have them all in one row with increasing the columns;
Or you can have them in one column with decreasing the columns;
Or any combination of the columns and rows that works best for your report’s layout.
You also have the option to change other grid settings such as lines, their appearance, etc.
Small multiple titles formatting
You can also set the label format for each multiple (in this case, the education’s category name).
This setting is under the Format of the visual, Small multiple titles.
Easier to understand and low maintainance
If I want to sum up the value of small multiples in the Power BI visualization, it would be as; Small multiples enable you to analyze data by multiple dimension attributes in a simple-to-understand way, and they are easy to maintain because they are part of one visual.
This feature (small multiples) is not only available for column charts. You can change the view to a few other charts too. That sometimes leads to even better visualization.
You can use colors and conditional formatting to have even a more understandable visual;
Small multiples at the moment (the date of publishing this article) is only available in these built-in visuals in Power BI;
Stacked bar chart
Stacked column chart
Clustered bar chart
Clustered column chart
100% stacked bar chart
100% stacked column chart
Stacked area chart
Line and stacked column chart
Line and clustered column chart
Another limitation I found in this visual is that we cannot scale the values, sometimes values in one multiple are much lower than the others, and if they are all at the same scale, it will make it hard to understand. This functionality is available on a custom visual such as infographic designer, which you can read more about it here.
In summary, Small multiples take your visualization to the next level. You can analyze data by multiple dimension attributes in an easy-to-understand and easy-to-maintain way. There are a few limitations, but since this feature is fairly new, we can expect more settings for this visual in the future.
https://nexumbs.com/wp-content/uploads/2021/09/small-multiple-select-data-point.png501805Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-08-25 15:17:242021-09-04 15:27:33Take Your Power BI Visualization to the Next Level using Small Multiples
Have you ever wanted to add a toggle feature to your Power BI Desktop Report, but don’t know how? Well the good news – it is easy to do, and even better news I will show you how to
A quick background – A toggle switch was made so users could see if the change was made successfully within the due date and the non-policy toggle was created to show if the change was successful, but not within the due date.
I will show you how to load an image, how to create a bookmark and hide objects, and how to add an action to the buttons.
There are a few key terms you need to know before we get started:
Selection Pane – gives you the option to show or hide objects.
Bookmark – is like a screenshot of your current report. Creating a bookmark, gives you the ability to view it later on.
Let’s focus our attention to the two visualizations that we want to toggle. We will want our visualizations to look like below.
On the first visualization, we want the per policy text and the toggle button turned on.
On the second visualization, we want the non-policy text and the toggle button turned off.
Find toggle images
1. You will need to find a toggle image on a search engine of your choice. Make sure you have two images – on and off. For example, below are the two images I used.
2. To save your images, go to your Power BI Images subfolder.
Upload your Toggle On Button
3. To upload the toggle button, go to the Insert tab.
4. In the Elements Group, select the Image Icon button.
5. A dialog box will appear to allow you to find the folder which holds your image. Go to Power BI – Images. Select it and then press the open button.
6. Your image appears, but most likely will need resizing.
7. To add verbiage, select the Text Box button.
8. A text box will appear. Add your verbiage and resize the text box to your liking.
9. Your image should look like below. The toggle button is on along with the Per Policy text.
Upload your Toggle Off Button
10. To upload the toggle button, go to the Insert tab.
11. In the Elements Group, select the Image button.
12. A dialog box will appear to allow you to find the folder which holds your image. Go to Power BI – Images. Select it and then press the open button.
13. Again, your image will need resizing.
14. Go to the Insert tab, and select the Text Box button.
15. A text box will appear. Add your verbiage and resize the text box to your liking.
Create Bookmarks and Hide Objects Toggle On
16. Select the View tab.
17. Select Bookmarks and Selection buttons.
18. To your right, you will see the selection and bookmark pane. The bookmark pane will be used to add two screenshots. One for the “toggle on” view and the other for the “toggle off” view.
19. In the Selection Pane, you can rename, show or hide your objects.
20. To rename, double click the relevant object and a white box appears. I recommend giving objects a relevant name so they can be easily recognized when hiding or unhiding your objects.
21. Hide the non-policy and toggle button off objects by selecting the eye icon.
*For this visualization, I also hid the visibility of “Overall Change Success% Non-Policy” and “Unsuccessful Changes non-policy” objects.
22. After you have finished the selection, we can now move on to the Bookmarks Pane.
23. In the Bookmarks pane, click the Add button, and then rename the bookmark to Per Policy.
24. In the selection pane, hide the “Per Policy Text” and “Toggle Button On” objects.
25. Show the objects Non-Policy and Toggle Button off.
26. Select the Add button.
27. Name your new bookmark “Non-Policy”.
Add an Action Toggle On
28. Select the Per Policy bookmark
29. Select the “Toggle Button” on image.
30. The format button pane will appear.
31. Go to the Action and expand the section.
32. In the Action section, go to Type and verify bookmark is selected.
33. In the Action section, go to Bookmark and select Non-Policy. When the toggle button is on, and your user clicks it the button will need to go to the Non-Policy bookmark.
34. Go back to the Bookmarks Pane, and select the Bookmark “Non-Policy”
35. Select the toggle off button.
36. The Toggle Off Button’s Format Pane appears.
37. Go to the Action Section and Maximize the section
38. In the Action section, go to Type and verify bookmark is selected.
39. In the Action section, go to Bookmark and select Policy. When the toggle button is off, and your user clicks it the button will need to go to the Policy bookmark.
Conclusion We selected an image to create our toggle button, then we used the selection pane to show/hide relevant objects. Next, we created bookmarks to take a screenshot of our pages, and last we setup actions to have the toggle switch between pages when selected.
Congratulations! You now have a toggle button that you can switch on or off! Have fun using your new skills!
https://nexumbs.com/wp-content/uploads/2021/08/power-bi-button-sample.jpg8901416Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-08-23 11:11:222021-08-23 11:11:22How to Add a Toggle Feature to Your Power BI Report
Have you ever been facing the problem of showing small and large numbers together (e.g. 123,456, 789.99 and 34.56) in a nice way, lets say with a 3 digit precison as 34.5 and 123G (or 123E6).
When working with large numbers you can divide them with 1,000 or 1,000,000 and round them to zero (or two decimal points), but then small values could be presented as “0”.
You can also add bars and colors to the background (or text colors) reflecting the size of the values… But usually when you have a design you think will work, the user will filter down the data to something you haven’t expected and then again, things are not showing as you and your users would expect. So all work for nothing.
Wouldn’t it be nice to be able to show values like this, in this case using a 3 digit precision:
And on-top of that, you can also give the user the possibility to choose how the formatting shall be done using standard filter
User defined dynamic formatting
The alternative could have been one of the two formattings below:
Matrix 2aMatrix 2b
As can be seen above:
In Matrix 2a, not reducing the number of digits makes it hard to read, even though I’m using background color and thousand separators.
In Matrix 2b values less than 10’000 are “hidden” as 0.00M.
Note that simple user-defined formatting in kilo, Mega, etc, using a filter has been possible before, but with some side-effects like
Exported values (to excel or CSV) beeing reduced by 1’000, 1’000’000, etc
Hovering over values in diagrams will show values reduced with 1’000, 1’000’000, etc.
Both (1) and (2) above might in some cases be the intention, but then the user needs to be aware of what the numbers means.
The method described here doesn’t have the side-effects above, i.e. for side-effect (2), see the exported Excel below, keeping the exported numbers at their original precision:
Exported Excel File
And maybe most importantly, using the method described here, diagrams like the bar-chart below will show the values formatted as you decide:
Bar Chart using Value Dynamic Formatting
This (and more) is possible using advanced formatting string and some simple DAX in Calculation Groups.
Microsoft has already described how to do dynamic formatting, and the SQLBI guys has very good articles as well (here and here). This post goes beyond that and shows how to format dynamically even based on the values.
Note/Warning: At the time of writing, some bugs exist in the standard visuals for Power BI, but Matrixes and Multicard visuals seams to work and should be safe to use (chart diagrams are not ok even though I have showed one chart diagram above). The bugs are confirmed by Microsoft although no ETA has yet been confirmed. External ticket number is “2104200050002054” if someone would like to refere to it. I have also created a idea to fix the formatting issues, please vote.
Implementing in your reports
Implementing Value Dynamic Formatting requires a Calculation Group. But I will start with showing how this can be implemented in the reports if you alread have this Calculation Group created. The reason for this is that creating Calculation Groups requires 3’rd party tools (i.e. the Tabular Editor) and this could be seen as a bit complex if you haven’t done it before. So instead, please use my already prepared PBIX file and determin if this is for you. For information on how to create the needed Calculation Group, please see “Creating the Calculation Group” below.
The datamodel used in this example is very simple, a super small Fact table, one Calendar dimension an a Dim Name table (this could also have been just one flat table). But most importantly, there’s also a disconnected table called Sufix, and this is a table of the special type “Calculation Group”.
What’s an Calculation Group
A Calculation Group looks just like a regular table, having two columns. One index column (called “Ordinal”) controling the sort ordering, and one content column (in this case renamed to “Sufix”). Each row in the Calculation Group represents a Calculation Item (i.e. “Kilo”, “Million”, “Precision 3”, etc). Using the data view in Power BI the Calculation Group table looks like below:
For now, we can see each row in the Calculation Group (i.e. each Calculation Item) as something capable of overiding explicit measures with new code, and owerwriting existing formatting with new formatting. In simple cases like in “Kilo”, the Calculation Items overrides the measures with a formatting of “#,0,.##k$”, but in case of “Dynamic1”, “Precision 3”, etc, it’s more complex codes involved. The only thing we need to know at this point in time is that the sequence of “,.” in the formatting string (a comma just before the decimal point) means divide with 1,000 before displaying. The same way a sequence of “,,.” (two commas just before the decimal point) means divide with 1,000,000 before displaying, etc.
The usage of Calculation Groups to format values
To activate this formatting we simply filter a Calculation Item (i.e. a single row from the Calculation Group) for a visual. This can be done as either a visual level filter, a cross filter on the canvas, a page level filter, or a report level filter. This can also be done explicitly within a measure, but that is not so relevant in this case.
So as of now, the only thing you need to do when changing this original matrix:
Into this matrix using 3 digit precisions:
Is to apply a filter on the Calculation Group to the visual, e.g. like below:
Applying a Calculation Item as a visual level filter
To have the formatting affecting all visuals on the canvas, just place the Calculation Item as page level filter. To let the user decide how to format, place the filter as a visual filter on the canvas like below:
Applying a canvas filter to let the user define what formatting to use
Some notes about what configurations has been needed for the page above.
I have intendendly turned of the interaction from the filter visual to the two lower matrixes (marked with yellow in the diagram above). For these matrixes I have instead in the left matrix used the built-in Field Formatting setting to change the Display unit to Millions.
Please also note that for all visuals affected with this formatting Calculation Group, you need to make sure that the “Display unit” configuration setting is set to “None”. Sometimes this setting has a default value of “Auto” and this will cause problems sometimes hard to see before releasing the report.
You find this setting under different setting categories depending on the visual, e.g. under “Field formatting” for matrixes and under “X/Y axis” as well as under the “Legend” settingsfor bar/column charts.
But you also need to know this
That was almost everything you need to know about using Calculation Groups to format values in your visuals, but there are things to point out.
The Y-axis scale does not work (marked with red in the picture above). It’s showing 1,000,000,000G instead of simply 1G. This is due to confirmed bug. The axis gets the “G” from the formatting but forgets to divide the value with 1’000’000’000 as it should since the formatting string contains “,,,.”.
The legend value for Steve is not correct (marked with red in the picture above). It shows 123457k instead of 123k. There’s many fault here since the raw value is 123’456.70. (where’s the “6” and where’s the decimal point?).
The tooltip value shown when hovering (marked in green) is correct even though the lgend values are not, see (2).
Safe usage untill the bugs are fixed
For Multiline card visuals and Matrix visuals I have not seen any bugs, so I woud consider them safe to use. For the other visuals we can still benefit a bit from this formatting method, and there are two methods I would recomend:
Turn of Y-axis and legend values in your bar/column chart visuals (and also set the “Display unit” settings to “None”). Since it’s only the Y-axis and the legend values that are not showing correct values, the diagrams will still show the correct size of the bars and the corect formatted values for the tooltips.
As an alternative, if showing the Y-axis (and/or showing the legend values) is a requirement, you can create an explicit measure, maybe called “measure1_format” and make sure that the Calculation Item formatting only affects this measure. This can be done by adding code to the Calculation Group formatting to only affect measures ending with _format. Then add this measure as an explicit tooltip measure. Now the diagram will work like it did before you started to use the Calculation Groups, but with the addition that the tooltip will be formatted in a better way.
Temoprary work around
Creating the Calculation Group
Disclaimer: Before working with Calculation Groups, make sure you read and understand what it means, and make sure you take a backup of your PBIX file. Everything described here is safe, but you will be dealing with 3’rd party tools not supported by Microsoft, although the API the tools are using are supported by Microsoft.
Creating a Calculation Group requires for now the 3’rd party tool called Tabular Editor. The SQLBI guys has some very good articles on what Calculation Groups are and how to create them using Tabular Editor, so I will not dig that deep into that. Instead I will create a step-by-step instruction for how to create the calculation items used in this post, and a pre-requsit is that the Tabular Editor is installed.
Step 1. Create a Calculation group
From the External Tools tab in Power BI Desktop, click on “Tabular Editor”.
In Tabular Editor, right-click on the Tables folder and choose “Create New” and then “Calculation Group”.
Give it the name “Sufix”.
Expand the Calculation Group and rename the column called “Name” to “Sufix”.
You have now created an empty Calculation Group and renamed the column to “Sufix”.
Step 2. Create the Calculation Items
Right-click on Calculation Items and choose (New Calculation Item), give it the name “None”.
In the editor window, write: SELECTEDMEASURE().
Repeat the same steps (1) and (2) for the calculation items “Kilo”, “Million”, and “Precision 3”.
You have now creted the four Calculation Items “None”, “Kilo”, “Million”, and “Precision 3”, and configured them with the DAX code SELECTEDMEASURE(). This will make sure that the Calculation Group will work for all explicit measures. Without the SELECTEDMEASURE(), you will only see blank values.
Step 3. Configure the formatting for the simple Calculation Items
Klick on Kilo and in the preference dropdown window in the editor windows, choose “Format string expression” instead of “Expression”. Now you can write the formatting code.
Write exactly this, including the double quotes: “#,0,.##k”
This will format the values with thousand separators, and with two possible decimal digits and add a “k” after all values. This will also divide the value with 1’000 due to “,.”.
Now do the same but for the Calculation Item “Million”. Use this exact formatting string: “#,0,.##M”.
You have now configured the two simple Calculation Items with the formatting string. The Configuration Item “None” does not need a formatting string, so if configured, you could also just add the DAX-code BLANK() instead of not configuring it, the result will be the same.
Step 4. Test the three configured Configuration Items
Before proceeding with the more complex Configuration Item “Precision 3”, we should test what we have done.
Save the data model by pressing Ctrl-s or push the save cube icon in the ribbon.
Switch back to Power BI Desktop and push the “Refresh now” button. You see this button since you added/changed a Calculation Group and the Calculation Items.
You now see the newly created Calculation Group besides your other tables. Just add the “Sufix” column from the “Sufix” Table (Calculation Group) as a visual level filter as in the picture below.
Check the “Require Single Selection” checkbox and then select e.g. “Kilo”.
Test both “None”, “Kilo”, and “Million” to make sure all works. Also add the Calculation Item as a filter on the canvas, but then also remember to remove the same as a visual level filter.
Step 5. Configure the formatting for the “Precision 3” Calculation Item
Repeat the previous steps for creating the three simple Configuration Items, but this time use this code as the “Format string expression”:
VAR V = ABS( SELECTEDMEASURE () )
V >= 1E10, "0,,,.0G",
V >= 1E9, "0,,,.00G",
V >= 1E8, "0,,.M",
V >= 1E7, "0,,.0M",
V >= 1E6, "0,,.00M",
V >= 1E5, "0,.k",
V >= 1E4, "0,.0k",
V >= 1E3, "0,.00k",
V >= 1E2, "0.",
V >= 1E1, "0.0",
V >= 1E0, "0.00",
This is a bit more complex formatting code, but still very simple. Depending on the absolute value returned from the explicit measure, different formatting strings will be used, all delivering a 3 digit precision.
To test this, repeat the steps you used when testing the three other Configuration Items
Note: The formatting codes showed hera are a bit simplified compared with the code you would used in your report. In your report you would like to protect the Calculation Group so it doesn’t “fire” for unwanted measures. Analysing the PBIX file in this blog you will see how that could be done, but below you see one simple example that only “fires” the formatting whenever the measure name starts with “Sum “:
Please also note that more than one Calculation Group can be applied on one single visual and you can/must control the execution order. As I can’t think of any Calculation Group that you would like to apply before the formatting Calculation Group (i.e. you would like this to be the most outer CALCULATE() when the applied Calculation Groups are re-written) I strongly recomend to set the Calculation Group’s Precedence to 10’000 or higher.
Behind the scene
To understand more what’s happening when applying the formatting strings using Calculation Groups, we can use the tool “DAX Studio” provided by SQLB.com. Using this tool we can see that for e.g. the bar chart diagram, the data sent to the visual looks like below:
Data sent between to the visuals using Calculation Groups formatting
As can be seen above, the formatting string is added as an extra column to all rows. If more than one values was to be shown in the visual, one extra column will been added for each value columns. Note that since I’m here using the “Precision 3” dynamic method, the formatting is specific for each row.
https://nexumbs.com/wp-content/uploads/2021/05/Power-BI.png5781029Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-05-02 11:16:452021-05-04 11:23:39Creating Value Dynamic Formatting using Calculation Groups
Want to make yourself more productive with Microsoft Teams? Use these eight tips to master command-line and keyboard shortcuts, connect to external apps, and make useful content easier to find.
If you’ve begun using Microsoft Teams to collaborate with others as you work from home, you probably mastered the basics quickly. (Looking for introductory material to get your team started? You’ll find a big collection of videos and short tutorials as part of the official Teams documentation.)
In this article, my goal is to show you some time-saving shortcuts that go beyond the basics and can make you more productive.
Use the command line
It might not be immediately obvious, but the Search box at the top of the Teams desktop app doubles as a command line. Click in that box and then tap the slash key (/) to display a list of all available commands.
Developers, of course, are probably the most comfortable with a command line, but even learning a few commands can make you more productive at tasks you do over and over. You can use /chat or /call, for example, to begin communicating with someone, or use /busy or /away to change your status.
Learn some keyboard shortcuts
Now that your hands are properly positioned on the home row, just the way you learned in typing class, why not memorize a few keyboard shortcuts, too?
Pressing Ctrl+E takes you to the Search box, for example, just as it does in File Explorer and your web browser. You can use Ctrl+number to go to the corresponding node in the navigation pane on the left. In the default arrangement, Ctrl+1 goes to the Activity pane, Ctrl+2 takes you to Chat, and so on. Press Ctrl+Shift+X to toggle between the bare compose box and the full editor with all its formatting options.
Just as in your web browser, you can hold down Ctrl as you tap the plus or minus keys to zoom in or out, then press Ctrl+0 to go back to normal (100%) magnification.
And perhaps the two most popular of all: Ctrl+Shift+M to mute/unmute your microphone in a call or chat, and Ctrl+Shift+O to switch the video camera on/off..
To see the full list of available shortcuts, type the command /keys, or press Ctrl+. (period).
Add a subject to a conversation
When you select a team and then click a channel, the default Posts view shows public conversations for that channel. Everyone who is a member of that team can read and participate in those conversations.
When posting a new conversation/thread, it’s a good idea to add a subject, as I’ve done in the opening post here. That makes it easier to spot a specific conversation by scrolling through a channel, and also makes it easier to use the search tools to find that conversation.
https://nexumbs.com/wp-content/uploads/2021/05/microsoft-teams.jpg9451800Nexumhttps://nexumbs.com/wp-content/uploads/2021/03/logo.pngNexum2021-04-07 21:42:242021-05-04 21:54:03Microsoft Teams: How to master remote work beyond the basics
Nexum BS has been helping companies succeed through the ERP and Power BI Solutions. Experts in field of business process and change management, our team guarantee rapid implementations, customizations, execution, consultation and training
2645 Executive Park Drive, Suite 673, Weston, FL 33331