Charts in Power Apps
There are several different ways that you can accomplish building charts in PowerApps, ranging from the high-end Power BI, all the way to something simple like a series of rectangles with variable widths. In this post and associated video, I’ll go over your current options, what you’ll need in order to accomplish them, and what’s possible. For Power BI, I’m not just showing how to add the tile to a PowerApp, but how to make it display dynamic data.
First Option: Power BI Tile
In order to use the Power BI Tile input, you do need to already have a Power BI dashboard tile that you’d like to display in a Power App. So, these steps are assuming you’ve already got that. Also, each end user who will be using this particular Power App will need to have a Power BI license.
1. In PowerApps, on the Insert tab, click the Charts drop-down, and choose Power BI Tile.
2. The data panel will pop out, to select your already existing Power BI Workspace, Dashboard, and Tile.
In this example, I selected My Workspace, Office 365 Adoption Preview, and the tile called Last month – % of users active in one or more products.
This is static information, though, and we may want to display some kind of dynamic data, like information about users in a specific department, or even relative to the department that the currently logged in user is in. You could just stop right here if you simply want to display static data.
3. In this step, you’ll be making the data dynamic, if you’d like. You need to know the name of the table and field from your data source, that you’d like to filter. Select your Power BI Tile, and go to the property called TileURL.
4. Copy the whole Tile URL to your clipboard, open up Notepad and paste it there. You’ll be adding your filter to the end of the URL, so the URL will be slightly different for each value.
My table name is UserState, and field is Department. So, if I want to show only the data where the department is equal to IT, here’s my syntax to add to the end of the TileURL:
&$filter=UserState/Department eq ‘IT’
5. Create a button in your Power App, and in this case since I want this to be the button to filter by ‘IT’, I’m naming the button IT. This button will be used to set a variable, so go to the OnSelect property, and this is where we’ll put our new string of text.
Set(varChartURL, “https://app.powerbi.com/blahblahThefullurlFromYourOwnTile&$filter=Yourtable/Yourfield eq ‘YourValue’”)
Notice that at the very end, there are single quotes around my value, which in this example will be IT, and then there’s another double close quotes before closing the parentheses.
6. ALT+Click the button you just created, so that it sets the variable.
7. Select the Power BI Tile again, and go to its TileURL property. For the value of the TileURL, simply type the name of your new variable, varChartURL.
8. If you’d like to try other departments / values for your field, copy your button and paste it on the same screen. Then, all you’ll need to change, is the part in the single quotes at the end, so instead of ‘IT’ in my example, I can use the department ‘Executive’. When you preview the app, as you click each button, you can see the tile’s data change. This can be potentially used with various other types of controls, like drop-downs or radio buttons instead of the simple buttons in this example.
9. As a bonus, instead of just picking from a list of departments, I can set it up to just look at the currently logged in user’s department, and show the data filtered by that value automatically. Add the Office 365 users data connection to your PowerApp.
10. Do steps 2 and 3 again, you can do this on a completely new tile if you’d like. Go to the TileURL property. You’ll need to concatenate that value, with the currently logged in user’s department.
Now the tile automatically shows a very specific set of data, instead of having to manually click a button to change it.
Second Option: Chart Controls
There are three built in chart controls, Column, Line and Pie. When you first insert one, expand it over in the tree view on the left, and select the chart control inside of the composite group, and go to the Items property. By default, it shows some fake data, like here, the PieChartSample. On this screen, the pie chart on the left shows the pie chart sample data. In the column chart on the right, I changed the data source to ‘Task List’ which is the name of a list I created in SharePoint. Notice how useless this is, by default, it just shows an item in the chart per every single item in the list. You would never want to do that with an actual chart, you’d probably want to group the items together by some value. For example, I may want to see a chart of tasks by their status, by month, or by who they are assigned to.
For the data in my next example, I have 3 SharePoint lists: Customers, Projects, Timesheet. The projects list has a lookup field called Customer Lookup, which looks up to pick the associated customer for each project that you create. The timesheet list has a lookup column called Project, which looks up to the projects list. The timesheet list also has a number column called Hours.
1. Create a button. Just call it Collect. The commands we’ll be building to run at the click of this button, can later be used somewhere like maybe the OnVisible property for a screen, if you don’t want to have to click to generate the chart.
2. Go to the OnSelect property and do all of these commands. Collect all of my projects (watch the associated video below for my disclaimer in this part about if you’re dealing with a large list and how you’d deal with it differently). As I collect my list of projects, I’m adding a column to the collection that has the name of the associated customer from the customer list.
ClearCollect( colProjects, AddColumns( Projects, “CustomerName”, ‘Customer lookup’.Value ) );
Collect the timesheet. As the timesheet is being collected, an extra column will be added for the customer name, and I’m calling it “Cust”.
ClearCollect( colTime, AddColumns( Timesheet, “Cust”, Lookup( colProjects, ID = Project.Id, CustomerName ) ) );
Collect data grouped by customer. Each grouping will be called “Cust”, and each group will contain the timesheet items for that customer.
ClearCollect( colGrouping, GroupBy( colTime, “Cust”, “Customer” ) );
Collect to get the sum of hours for each customer. The hours data originally came from the timesheet entries.
ClearCollect( colCustSum, AddColumns( colGrouping, “Sum of Hours”, Sum( Customer.Hours, Hours ) ) );
3. Select your column chart control, and go to the Items property. Simply type the name of that last collection, colCustSum.
Now the result is a chart that is useful and shows a grouped set of data, with sums or averages or whatever you’d like to include. Microsoft’s GroupBy documentation referenced at the bottom, goes into detail and also has tutorials on how to learn this function.
Third Option: From Scratch
From scratch really seems like it would be arduous, but it is very simple, and gives you an extreme amount of flexibility. The idea is that you can take any kind of control, a rectangle, a label, even a button or circle, and make its dimensions variable. In this example, I have a list of tasks, and each task has a percent complete.
1. Create a SharePoint list that has a number column called percent complete. Mine is called Task List.
2. Insert a gallery in Power Apps, using the layout called Blank Vertical. For the Items property (the data source) of the gallery, pick your task list.
3. In the gallery’s property panel on the right, select the layout called Title.
4. In the gallery, insert a new label. Change the label’s fill color to a nice, dark solid color, like blue or green.
5. Find the label’s width property. Pick ThisItem’s percent complete field, as seen below. Multiply it times 200. 200 is just the number of pixels, so if you want the bar to take up even more real estate on the screen, you could increase the number of pixels.
Another cool thing that you can do in Power Apps, is animation. We created this custom component, which uses this from scratch method, but the chart is animated, with the bars moving, increasing across the screen.
Use this coupon and get your copy to download and add to any of your own Power Apps.
Fourth Option: HTML
There is a fourth method that I didn’t mention in the video. It’s HTML. There is a way to create dynamic charts, gauges, any kind of visual indicator, by using the control called HTML Text. Here is a blog post on Microsoft’s Power Apps site, describing a few different examples, like a gauge indicator and even a custom rating visualization:
UX Patterns: NumericUpDown, Custom Rating and Gauge Controls
Here is the full video demo of the first three above mentioned solutions, plus much more information and explanations:
3:54 Power BI Tile
13:02 Go into Power BI and dig into data tables and field names, to figure out how to do the TileURL syntax.
14:14 Add Office 365 users connector, in order to make the tile dynamically show data per the logged in users’ department
19:19 Chart controls intro basics
22:59 From scratch concept, starting with the animated bar chart component demo
27:32 Completely from scratch gallery based off of a task list, adding a label with a variable width
32:06 Set up the bar to show as red if the percentage complete is less than 50%.
33:40 Back to the built in chart controls, and deeper dive into how to group the data.
48:51 On-the-fly demo of creating a vertical bar gallery using the from scratch method.
Creating dynamic Power BI Tiles in PowerApps https://powerusers.microsoft.com/t5/News-Announcements/Creating-dynamic-Power-BI-Tiles-in-PowerApps/ba-p/370852
Show data in a line, pie, or bar chart in Power Apps https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/use-line-pie-bar-chart
GroupBy and Ungroup functions in Power Apps https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby
Microsoft 365 Usage Analytics https://docs.microsoft.com/en-us/office365/admin/usage-analytics/usage-analytics