Pie Chart - Bringing in multiple columns of one item

[Philipp7]

Hello all,

I am trying my first steps with PowerApps.

What I want to do:

I want to create a Pie Chart out of multiple columns of a SharePoint list. For one item of the list the pie chart should display 2 different columns. E.g. I have the columns OPEX and CAPEX. They should both be shown for one list item in one pie chart.

Unfortunately, I can only select one label or series which stands for one column. How can I add multiple columns?
Name of the list is: IT-Projects-List 1.0.0

Thanks in advance!

[AmDev]

Hi @Philipp7  - just to clarify, you only ever want to show the CAPEX and OPEX for one item/row on the pie chart?

[Philipp7]

Hi @AmDev, yes correct. 

[AmDev]

Hi @Philipp7 

This is based on the following back end structure example list named 'PieChartList': 

Add your Pie Chart and I have used a drop down to change the item:

Follow these steps:

• In the 'OnVisible' property of your screen create the drop down options as follows: ClearCollect(colProjects, {Project:"-Select-"} ); Collect(colProjects, PieChartList.Project) - then bind this to the items of your drop down
• Add the following code to the 'OnChange' property of the drop down:  With({SelectedProject:LookUp(PieChartList, Self.Selected.Project = ThisRecord.Project)}, ClearCollect(colPieChartItem, AddColumns(Table(
  {Project:SelectedProject.Project, ValueLabel: "CAPEX", Value:SelectedProject.CAPEX},
  {Project:SelectedProject.Project , ValueLabel: "OPEX", Value:SelectedProject.OPEX}),"Label", Concatenate(ThisRecord.ValueLabel ," - ", ThisRecord.Value))))
• Then in your chart set Items to 'colPieChartItems', set the Label to 'Label' and set the Series to 'Value'

That should be your core chart working and allow you to flick between and display the different items. You could also leverage 'colPieChartItem.Project' in the chart Title.

Hope this helps, but let me know if there are any queries - Good Luck!

[Philipp7]

Hi @AmDev ,

thanks for your reply and the efforts.
I have followed your guide but it is still not working. I guess I have made a mistake when adjusting your code.

Here some remarks:

• I have no primary key in my table.
• Your "Project" column is named "Title" in my list. The other columns are named "Budget OPEX" and "Budget CAPEX". I have some more columns in the list.
• The name of my list  is: "IT-Projects-List 1.0.0"
• I cannot see and select values in the dropdown.

Here my adjusted codes:

1. ClearCollect(colProjects, {Title:"-Select-"} ); Collect(colProjects, 'IT-Projects-List 1.0.0'.Title)
2. With({SelectedProject:LookUp('IT-Projects-List 1.0.0', Self.Selected.Title = ThisRecord.Title)}, ClearCollect(colPieChartItem, AddColumns(Table(
   {Title:SelectedProject.title, ValueLabel: "CAPEX", Value:SelectedProject.'Budget CAPEX'},
   {Title:SelectedProject.title , ValueLabel: "OPEX", Value:SelectedProject.'Budget OPEX'}),"Label", Concatenate(ThisRecord.ValueLabel ," - ", ThisRecord.Value))))

I got the following error message for the second code:

Remove or correct the reference to the name that isn't valid.

Is it also possible to do this without the dropdown? I have on my first page a BrowseGallery from where I can select an entry of the list. And the on the Detail Screen i want to show the chart and some other things. Can i refer to the selected list item with "BrowseGallery1.Selected"?

Thanks for your help!

[AmDev]

Hi @Philipp7 - yes you can achieve what you are after i.e. passing a value from one screen to control what happens in your other detailed screen.

Lets try and get you drop down scenario working and I will follow up on the change in approach.

In the drop down is 'colProjects' in the Items property? - also is the colProjects formula in the 'OnVisible' property for the screen?- If so, back and forth between screens to trigger the OnVisible formula and that should then show the items in the dropdown.

 Also, I see the following has been used twice in your formula with a small 't' 'Title:SelectedProject.title' - but your column in the Collection is Title.

Try making these changes and flick between screens to fire the original collection for the drop down. 

Would be good if you can get this working and I'll follow up with the change.

Good Luck!

[AmDev · Accepted solution]

Hi @Philipp7 

Please see these changes to achieve the following - Click on the respective item in the Gallery and navigate to another screen, which shows a pie chart of CAPEX and OPEX for the selected item:

• Add the following to the Onselect property of your gallery line button, icon or to the gallery itself: Navigate('Screen-PC',ScreenTransition.UnCover,{varlSelectedProject: ThisItem})

• The portion in red above is a context(local) variable that you can pass to the screen you are navigating to - in this case, it's actually the full record from your datsource, which you have selected in the gallery that you are passing to your details screen
• For the OnVisible of your details / PieChart Screen add the following code:  ClearCollect(colPieChartItem, AddColumns(Table(
  {Project:varlSelectedProject.Project, ValueLabel: "CAPEX", Value:varlSelectedProject.CAPEX},
  {Project:varlSelectedProject.Project , ValueLabel: "OPEX", Value:varlSelectedProject.OPEX}),"Label", Concatenate(ThisRecord.ValueLabel ," - ", ThisRecord.Value)))
• Notice this code is slightly simplified as there is no need for the 'With' and 'lookup' as you have the full record that you are interested in at your disposal in the form of local variable, 'varlSelectedProject'.
• Everything else in your PieChart remains the same as the collection it uses doesnt change (just the way in which is is created).

Hope this all makes sense - Good Luck!

[Philipp7]

Hi @AmDev,

thank you very much. It is working!
I have one more problem:

I use a Gallery at the start site from where I select the list item. On the next site (General) some general information can be found and you can switch between 3 screens (General, Budget, Resources) with buttons. When I refer with the Navigate code from the Gallery directly to the Budget screen it is working. If I refer to General and then click on the Budget button, I land on the Budget site, but with an error. I guess the problem is that the variable cannot be read. 
I tried to hand over the variable with your code above, but it is not working. Any recommendations?

Really thank you for your time and efforts. This is helping me a lot! 

[AmDev]

Hi @Philipp7 

Glad that concept worked for you.

If you want to use a variable across more screens in your app you can use the Set function to create a global variable onselect of your gallery 'Set(vargSelectedProject, ThisItem)'. Don't think you can include a global var in navigate function, so just leave that out of navigate and add the Set function after it in the onselect. Then be sure to update any var references in your other code.

Hope this helps

[Philipp7]

Thanks for the quick reply. It works! 😀
So I think I don't have to update the variable, because the user will only change the list item through the start page gallery and then the variable will be adjusted every time.