Trending February 2024 # Creating Dashboards And Apps In Power Bi Service # Suggested March 2024 # Top 2 Popular

You are reading the article Creating Dashboards And Apps In Power Bi Service updated in February 2024 on the website Moimoishop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Creating Dashboards And Apps In Power Bi Service

In this blog post, I’ll discuss dashboards and apps in Power BI. These features are found in the Power BI online service, specifically, in the workspace area.

Dashboards are used to consolidate information while apps are used to summarize dashboards.

I’ll also explain what else you can do with dashboards and apps in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

For example, you want to collect information from different pages within a report or different reports in your workspace. You can use a dashboard to compile this data.

To create a dashboard you need to pin the necessary information.

Within each visual, there is a pin icon.

This enables you to pin that particular visualization.

I’ll pin this card visualization.

I can pin this visualization to an existing dashboard or a new dashboard.

I’ll create a new dashboard called UBG Demo Dashboard and pin the visualization.

I’ll also pin this chart.

Under the tile image, there’s an option to select a theme.

I’ll select the current theme to keep its original colors.

Next, I’ll also pin the map and another card.

After that, I can go to the dashboard.

On the dashboard page, the pinned visualizations are shown as tiles.

And we can move them around the area to organize the dashboard as we like.

I can also find my way to the dashboard by using this drop-down menu.

Dashboards are like reports, wherein they both present information.

The difference between them is that dashboards enable you to consolidate key insights from a range of different pages or reports.

In other words, it’s done on a larger scale.

Dashboards are a powerful when it comes to consolidating information.

There are also new features that you can use.

Using the Subscribe feature, you can also create email subscriptions which emails out a link of your reports.

All of these can be automated using a software called Gateway which automatically updates your reports.

Next, I’ll talk about apps.

You can create an app for a workspace which can summarize all the key dashboards and reports that you have.

In other words, apps are a way of summarizing key insights from different workspaces.

In the reports page, there is an option to include each report in the app.

In this case, I’ve included a few reports in my app. I’ve also included a dashboard as well.

Since I’ve already created an app, I have the option of changing it using Update app.

If I haven’t created an app yet, this button will show as Create app.

And then it will take you to this apps page.

It will ask you to create a name for your app and then you can customize it according to its purpose.

I’ll open an app that I’ve already made to show you what’s inside.

The app shows a summary of all the different reports that you’ve placed there. It’s a new experience compared to dashboards and reports.

There is potential in the functionality of apps in Power BI.

And you can quickly navigate back by selecting Apps and it will lead you back to the apps page.

I’ve also grown to use apps as a distribution method as well. It’s helpful when you have a lot of data to compile.

I’ve explained how dashboards and apps can be used in Power BI. It’s up to you to customize it according to the needs of your organization. There are many features that can help you present your reports effectively.

Dashboards and apps are a great tool to collect a variety of information from different places and consolidate them in one place.

I hope you’ve gained some valuable insights from this blog! Good luck!

Sam.

You're reading Creating Dashboards And Apps In Power Bi Service

Handling Http Errors In Power Query And Power Bi

If you are working with web servers, either because you are trying to scrape data or you are using a web based API, you will be sending and receiving data via HTTP.

HTTP is the Hypertext Transport Protocol – it’s just the name of the system used by web sites to transfer data. You use it every time you visit a web site

If your request results in an error, the web server (or API) will generate an error which is returned to Power Query.

The default behaviour is for Power Query to then spit out a message like this

If you are not familiar with HTTP or this type of error then this can be confusing. What exactly is the problem?

This Works In Power BI and Power Query in Excel

I’m going to do this in Excel but you can do the same in Power BI. The main query uses exactly the same code in Excel and PBI. But the method to create the static data table of HTTP Error Codes is different.

In Excel I use #table and in Power BI I use the Enter Data button on the Ribbon. To read more about the different ways to enter static data check out this blog post Static Tables in Power Query, Power Pivot and Power BI.

Watch the Video

Download Sample Files

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Power BI Desktop File.

If you could handle this type of error in your code and provide a little more information to the end user, perhaps that would help them troubleshoot the issue and resolve the problem.

What if your error message was this

The message gives the user some idea of things to check (spelling) and tells them a way to confirm the URL is correct (type it into the browser).

Looking at the query, the URL it’s trying to access is

Typing this URL into my browser gives this error

You may have already spotted that the URL is incorrect. It should end with microsoft-365 not microsoft-356

By giving a more informative error message and some steps the user can take to troubleshoot, we can help them fix, or at least understand problems that may occur.

Manual Status Handling

Manual status handling means you are going to write your own code to deal with errors. You have to tell Power Query you’re going to handle them and you do this by specifying the ManualStatusHandling value when you make the Web.Contents request.

Web.Contents

Only Web.Contents allows you to manually handle HTTP responses. Neither chúng tôi or Web.BrowserContents support this ability.

For example to tell Power Query that you will deal with 400 (Bad Request) and 404 (Not Found) errors the request would look like this

Where the list of error codes you’ll handle are specified as a list of numbers {400, 404}

Handling errors means that you need to write you own error messages. To store these I’ve created a static data table that stores the error codes and the messages I want to display should the associated error occur.

The table is stored in a query called HTTP_Errors and looks like this

The ErrorCode column is a list of the error codes I’m handling so I can change the Web.Contents request to reflect this by replacing the { 400, 404 } list.

To check if an error has occurred you can use the Value.Metadata function

This gives you data like this, and I’m interested in the Response.Status

You can see the web server has responded with a 404 error. To access this value directly you can do so like this

Now that we can get the response code from the web server, we need to check if it is an error we want to handle. To do this you can use List.Contains to check if the ResponseCode is in the ErrorCodes column of the HTTP_Errors table.

If the web server’s response code is an error we want to handle then the code needs to display the associated error message.

To access the error message, first I’ll use List.PositionOf to get the row number for the error code.

Because table columns are lists you can use list functions on them. Lists are indexed from 0 so error code 404 is on row 3.

If the web server’s response is not an error I want to handle then the code will just return the response as it is.

Putting this all together the code looks like this

If an error occurs that isn’t listed in my HTTP_Errors table then Power Query will deal with that in the default way.

If no error occurs then the Response step contains the web server’s response and further transformations can be carried out on it.

Power Bi Reporting Templates Expanded – Power Bi Visualization Concepts

In this tutorial, I dive into how to customize Power BI reporting templates efficiently. You may watch the full video of this tutorial at the bottom of this blog.

When you create tables inside Power BI, you can’t format or design them exactly the way you want them to be. You can only use the default order or sequential order that Power BI generates within the table, which can be determined by a value, by alphabetical order, or an ascending or descending order.

That’s why I developed Financial Reporting Templates. These are a development concept created by Enterprise DNA. With this technique, you can now create customized financial reporting templates using tables inside Power BI.

This tutorial session is taken from a much longer and more detailed Enterprise DNA member-only workshop. Here, I demonstrate key concepts around how to visualize financial information and data effectively in Power BI.

First, let’s have a look at this template that I made for financial reporting.

Financial reporting is a perfect example for this demonstration. However, there’s a lot of custom templates or custom tables that you might want to create with a variety of information, such as a summary table of your key metrics.

In the past, this was very difficult to do if you wanted to grab specific measures out of different measures and integrate them into one report and put them on top of the other.

Now in this sample template I’ve broken it down by Total Revenue, Cost of Goods Sold, and even more, which is where the true customization comes in. I’ve left a space in the table, then I have Total Gross Profit.

I’m integrating a percentage with dollar amounts as well, which is again with a customization.

Let’s get to how I created it…

To get to this point of having to customize Power BI templates, it requires a lot of re-configuring in the background, the model set up, and the technique to grab revenues and costs, and then integrate them into one.

The first thing to do is to actually create the template – a unique table that looks somewhat like this – but of course you can customize in whatever way you want. You can put different columns or have some items indented in a different way.

You also need a Normalized Column, where we’re going to run a logic through. We’re going to identify which line we’re on in our custom template based on this normalized column. 

So this is the set up of a customized template, but then you need to find a way to integrate this into your report. This is where the data model comes in.

The structure of the data model is key to integrating your customize template into your table. As you can see in my data model, the Financials Template is right at the bottom and it has no relationship to any other tables.

This is where I put my template column and use a formula to work it out at every row in the table.

So let’s have a look at the DAX formula I created to solve this.

I used the SWITCH TRUE logic in the formula, which enables me to integrate the customized template that I made into the table. It works out at each row in the table, identifying the items.

This is what the SWITCH TRUE does. For instance, if I’m on the Total Revenues row, the calculation DIVIDE Revenues by 1,000 is applied. If I’m on the Total Cost of Goods Sold row (COGS), it will show DIVIDE Revenues by 1,000 etc.

It’s the SWITCH TRUE logic that enables the allocation of results to the rows in the table.

The final piece of the puzzle for this Power BI template technique is the Row Index, which sorts out the template in the table.

If you don’t have this, it’ll get confusing and won’t sort the items in a unique way. And what I did was, I hid it so you can’t see it in the table, but it’s actually there.

If you look closely, you can see that I have the Row Index and I’m able to sort 1, 2, 3, 4, 5, and so on. That’s how I get the exact template set up as I want.

This tutorial is a breakout session from a members-only Enterprise DNA workshop around financial insights. Here I focused on the technique that I developed to customize Power BI reporting templates. The technique involves the integration of data model, DAX formula, and template creation.

I hope you able to understand how valuable this concept is and implement it in your own business. You will find it helps a lot with your financial reports and any accounting presentations you might have.

Cheers!

Sam

Current Trend Analysis In Your Power Bi

In this unique example, I want to show you how to project a historical trend in Power BI and repeatedly project it forward. This particular example came from a question in the Enterprise DNA support forum. You may watch the full video of this tutorial at the bottom of this blog.

An Enterprise DNA member needed to carry out this analysis in the real world using historical information, which could be for example, quarterly or half-year results. They then had to take that older information and project them forward for some years.

This is an excellent example of how you can reuse time intelligence functions in many different ways.

You can also enable this logic to be filtered by a particular region, a specific store or for a particular customer set.

The visualization for your trend analysis is practically useful when you have a grouping, a quarter, or a sales period and you want to project or predict the results going forward.

This solution that I have discovered can be applied in a number of different ways. The current example may be about predicting a quarter, but you could project anything continuously going forward using a similar technique.

There’s a lot of interesting ways to apply a trend analysis inside your Power BI report. Now, take a look at this table below.

Firstly, you can see the columns for the specific dates of the year and its corresponding total sales. But, I also need to showcase what day it is in a particular quarter. That is why I created the Quarter Day column. 

I’ve gathered that data from the Dates table. In this table, you can see the Quarter Day column. Let’s see how I worked this out.

A quarter day is a significant figure in business. It signifies the day that officially begins a three-month period of the year (a quarter). It is important to really work out this dynamic calculation of quarter day since I intend to project its trend every single quarter in every year.

So, in order to find out the quarter days, I used the formula below.

It’s just going to find any particular date, and then subtract it to the very start of quarter. Lastly, just add 1.

As I go down the Quarter Day column, you’ll see that it continues to go all the way up until about 92. And then, it goes back to 1 at the very first start of the next quarter. 

Now that I have calculated the quarter day numbers, I need to decide on the particular time frame that I want to project forward. To do that, I need to use the formula below for the Quarterly Forecast.

First, I use the CALCULATE function for the Total Sales.

The FILTER function removes ALL the filter in the Dates table initially, and then re-applies them to a very specific time frame, which is 2024 for year, quarter 3 for quarter, and the very same day of a particular quarter.

The MIN function actually evaluates to the quarter day of every single day as we move through any month into the future and evaluates their correct quarter day. 

So in any particular day in the future, it will always jump back to the 3rd quarter of 2024, on the same quarter day.

In this example here, I’m showing a very specific time frame instead of just repeating historic numbers. In the example visualization below, I’m actually comparing the quarterly forecast to the total sales.

The graph seems a bit busy so you can actually improve it a little bit. This is just one way to see it in a comparison perspective. 

Aside from that, you can also use a moving average measure inside it. This can help users to calculate moving averages of a specific time frame, and then just continually project that out.

Moreover, you can predict anything out aside from sales and that’s where measure branching comes in. Actually, you can also add factors and even percentages for your particular results.

To sum up, all these trend analysis can be done very efficiently within Power BI when you set up your calculations correctly.

The great thing about this technique is that I feel this is such a real-world application in Power BI. Likewise, it’s also about how efficiently you can create this sort of logic using DAX formulas. Moreover, you can showcase it in dynamic ways when projecting your trends forward.

Good luck with learning this one.

Sam

Sorting Date Table Columns In Power Bi

In today’s blog, I’ll discuss a question that comes up all the time in the Enterprise DNA forum. How can you can sort the fields from an extended date table columns properly? You may watch the full video of this tutorial at the bottom of this blog.

This refers to the extended date table from the M code showcase that was developed by Melissa Dekorte, one of our Enterprise DNA experts.

Our example on this blog post is just a simple application that runs from 2023 to 2023 with a fiscal year that starts in July. The member wanted to learn how to sort the Month Name; they were also trying to sort one of the fields related to month.

This is how you can sort a date table column effectively.

Our extended date table has a lot of fields in it; frankly, they’re hard to keep in order sometimes.

This is why we created this cheat sheet that shows you what each of the fields looks like, as well as their format and content, on any given day.

In most cases, you can use this to figure out how to sort one field based on another.

We have the Month Name and the forum member was trying to use MonthnYear to sort it out.

Let’s find Month Name in our extended date table. Select Month Name in the Fields pane, and then go to Sort by column, and look for MonthnYear.

This comes down to a granularity problem. You need a one-to-one relationship between the field you’re trying to sort and the field you’re using to sort by.

In this case, for a given month in 2023, 2023, and 2023, we can have three different values for the MonthnYear field. This is why we need to find one that has a one-on-one relationship.

If we go back to our cheat sheet, we can see that MonthofYear is just the number of the month. This will have the one-to-one relationship that we’re looking for. This means January will correspond with 1, February with 2, and so on.

The next one, Month & Year, is a little more difficult. It is a compound field that has a short month and year.

You can see that for the Month & Year table column, everything has sorted out perfectly.

So now we have the first two done. However, the last one is a really ill-behaved field.

It’s problematic for a couple of reasons. We have two text fields concatenated together and the fiscal month field is not padded.

Ultimately, we want to achieve something similar to this MonthnYear, where we have a four-digit fiscal year and then our two-digit fiscal month padded after that.

The easiest way to do this is by using the power query.

We go to Transform data.

And then we go to our Dates table and add a Custom Column.

Let’s call this custom column Fiscal Year Fiscal Month Sort (FYFM Sort).

We’ll start with our text prefix which is 20 for the first two digits of our year and then we’ll concatenate that with a function called Tex.Middle. This function pulls a substring out of a larger text string. It’s analogous to the DAX function MID, and I’ll show you the difference between the two.

We’re going to use this on our fiscal year field.

In DAX, when we pull substrings, it’s a one-based index. To pull the third character, we will need to use 3. In power query, it’s a zero-based index so the third character will be 012.

We start with 2, which is the third character. We want to return two characters to get that second pair of digits off the fiscal year field.

The next step is to concatenate this with our padded fiscal month field.

In DAX, we will need to use a LENGTH function and IF function, then we can manually pad.

In power query, there’s a function that does all of that. This is the Text.PadStart.

Since the fiscal period is a numeric field, we’ll need to use a function called Text.From to convert the numeric value into a text value that we can pad. Then we’ll pull our fiscal period field.

We want to make sure that it has 2 characters. In cases where it has 1 character, we’re going to pad it with a 0.

If we sort our FM & FY field by column, it will now sort perfectly by fiscal year and then a fiscal month.

Conclusion

In this blog, I’ve covered the technique on how to sort date table columns in Power BI. This is a good strategy to do for difficult fields, which came from building a few custom functions. I hope you have found this general set of tools helpful in your own report development.

If you haven’t done so already, please subscribe to the Enterprise DNA TV channel, where we have more content coming out in the future.

Cheers!

Sam

Center Of Gravity Analysis In Power Bi

Finding the center of gravity of a cluster of locations is a common task for people working with Geographic Information Systems.

One example is when looking at the allocation of addresses to a depot or engineers to a working area. Although Power BI does not yet support true multiple dynamic layers in the map visuals, it’s still possible to demonstrate the methodology.

However, the calculated COG (center of gravity) may be on top of a building, or a mountain. It is merely an indication of a great spot. Further analysis into other conditions like practicality, surrounding infrastructure, availability of staff, and housing is required.

Before jumping into Power BI to demonstrate this, I’ll explain the math to those who are less familiar with this.

This part is a small scheme with four locations. This also contains the coordinates of every individual location.

You can see those coordinates represented on this map. As you see, location one is on -250 (X) and -150 (Y).

At this moment in time, the weighted center of gravity is right in the middle of all these locations.

I want to create this weighted center of gravity to move in a direction based on the information that I have, such as the number of deliveries to location 4. It can also be based on weight or revenue. Although in most cases, I consider the deliveries because they are the cost driver.

I need to complete the weights in order to calculate the center of gravity. 

So, if I enter 100 under location 1, you can see that the weighted center will move to this point. This is because the others are still 0.

If we set the weight of the others to 200, 200, and 500, the center of gravity will move to another location based on the weights that I have specified.

That explains how the weighted center of gravity works. 

To calculate the center of gravity, simply multiply the locations X and Y coordinates or a latitude and longitude by the weight for each point. 

After that, divide it by the total weight to get to the actual weighted center of gravity.

In this demo, I only used a small number of customer locations. 

A COG analysis is strategic and should be looking at data over an extended period of time, preferably over one year or more to identify seasonality. 

Therefore, I recommend starting with a standard analysis to gain insight before setting up the analysis. That can then be done with a smaller subset of the data, after completing the next steps.

Remove small outliers, like incidental customers, and check if there are any included deliveries to or for customers that you may lose. Moreover, add data for new customers based on the profile that they provided.

Run a few iterations of the model to show the different scenarios associated with any changes.

On larger datasets with thousands of points, consider grouping your data in Power Query. I used the postcode and group by, keeping the latitude and longitude of the customer with the highest number of deliveries.

This is a simple COG for a given set of data.

Here’s the data model. As you can see, there are no relationships.

First, I created a center of gravity query.

I need to calculate the center of gravity over a selected data set. In order to do this, just like what I’ve shown in the excel sheet, I have to take the total demands. 

I also need to calculate the center of gravity for the latitude. In this case, I multiplied the latitude by the number of deliveries.

Then, I did the same calculation for the longitude.

After that, I divided the given COGs by the demands.

Then, this will be returned in a one-row table using the ROW function. I made sure that the number of columns is the same as the number of columns in the dataset. I also made sure that the columns can have a text or a formula. In this case, I have the DAX measures for the longitude and latitude COGs.

Following that, I did the union. So, the final table that I used for the display is the table that contains the union of the center of gravity and the data.

After completing those steps, this table will display the names of the locations, revenue, demands, Longitude and Latitude, and the two columns that I added for this demonstration (COG color and the Size column).

The Size measure identifies whether the Total demand is equal to zero or more than zero. Hence, if it’s a zero, the size will be set to 1. If it’s higher than zero, it will be set to 0.15.  

Doing this will create a different size for the center of gravity, which shows as color Red and as size 1.

The red color is associated with the COG Color measure.

This basically indicates that if the size is one, the color of the COG indicator should be red. Otherwise, it should be blue. 

In the Data colors under the Formatting tab, just set the Format by to Field value, then select the measure for the Based on field option. For this scenario, I selected the COG Color measure.

Now, let’s take this sample analysis one step further.

In this example, I created five centers of gravities along with their existing dataset.

The map should display the center of gravity based on a selection from the slicer.

Achieving this scenario is quite simple. I’ve taken the center of gravity for each and every point on the map.

I created the same calculation for each measure.

After completing those five COG calculations, I added them up in this union table.

Subsequently, this table now displays the center of gravity on top of the data.

This provides a bit more flexibility in terms of analyzing data across all existing depots. 

If I select Blank and Depot Rotterdam, the map will display the data points and the center of gravity for Depot Rotterdam. It will also display the center of gravity for the other locations, which I’d like to keep as a reference.

The table will also display the data based on the selection. As you can see, it reflected the corresponding colors and sizes.

The Color measure is the same as the first example. If the size is 1, the color should be red; otherwise, it should be blue.

The other measures are merely a reflection of standard analysis like Total Revenue and the Total Demand.

For the final example, I added the locations and the center of gravities to the data. Therefore, I have the depots, the data, and the center of gravity. 

That results in a slightly different map. First of all, what I really like in this map is that it has the depots and all the points.

I can make selections in this slicer as well. 

If I clear the slicer, you can see that the map now displays the depots (green circles), and the center of gravity (red circles).

In one view, I have everything that I need to fulfill the analysis. 

If I select Depot Rotterdam and the center of gravity, the map will display all the points for Rotterdam. For reference, it will also display the center of gravities of the other locations.

Looking at the table, the size is now responding as I expect it to be.

The same goes for the Color COG.

All the data have stayed the same with the exception of the addition of the depots. But for the rest, I just modified the color and added selections that will be reflected in the map. 

The center of gravity that you see in these 2 cards is the actual center of gravity of the selected points. However, it will not reflect in another point on the map because the data is not updated.

The color measure that I used is different from the first one. This shows the color measure for the first example.

As for the color measure of the second example, I added a bit of logic to create the various colors on the map.

The legend is a small and simple table. Usually, legends in maps are not that good. So, I prefer to create my own if I can.

In this case, I’ve created a small legend description and the color.

For this sample scenario, I don’t need this legend to filter the map. I merely used it to display a guide for what the viewer sees on the map.

That covers the tutorial on how to calculate the center of gravity in geospatial analysis and modify the map visually.

You can also experiment with these settings. Keep in mind that you can achieve varying results depending on the way you set up the data. 

Check out the links below for more examples and related content.

Cheers!

Paul

Update the detailed information about Creating Dashboards And Apps In Power Bi Service on the Moimoishop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!