cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaingBennett Helper I
Helper I

Building a Chart based on Sharepoint Data and Drop Down Menus

Hi there,

 

I am in need of a little assistance in making a chart work from SharePoint data. I am trying to replicate the following chart from my Google Sheets.

Data by Year.png

 

 

 

 

 

 

 

 

 

 

 

 

 

This shows the total of an event and also the target data for each month.

 

I have set up some sample data as a Sharepoint List.

 

Sharepoint Data.png

I want to be able to change the chart with two drop-down menus. One which selects the Department and the other the Event. 

 

I have a basic chart set up based on this data which is below. 

 

Sharepoint Chart.png

The second Jan at the end of the chart is the data from the Sharepoint list from the Hythe Department. 

I would also want a slight gap between each pair of columns and the colour for the actual data to be different for each event types.

When I have the final data set up in Sharepoint the Events will be:

  • Valuation
  • Take On
  • SSTS
  • Let Agreed
  • Exchange
  • Viewings

And the Departments will be:

  • Lyminge
  • Hythe
  • Lettings

I hope all the above makes sense.

Regards

Laing Bennett

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Building a Chart based on Sharepoint Data and Drop Down Menus

Hi @LaingBennett ,

Is the solution I provided above helpful in your scenario?

 

If you want to change the Color of the Column chart based on the selected Departement value, I think the ItemColorSet property of the Column chart and the If function could achieve your needs.

 

Please consider set the ItemColorSet property of the Column Chart to following formula:

[
   If(
       DepartmentDropdown.Selected.Value = "Lyminge", Color.Red, 
       DepartmentDropdown.Selected.Value ="Hythe", Color.Blue, 
       DepartmentDropdown.Selected.Value ="Lettings", Color.Yellow
   ),
   RGBA(48,166,103, 1), RGBA(94,193,108,1), RGBA(246,199,144,1), RGBA(247,199,114,1), RGBA(247,180,91,1), RGBA(246,143,100,1), RGBA(212,96,104,1), RGBA(148, 110, 176, 1), RGBA(118, 154, 204, 1), RGBA(96, 197, 234, 1)
]

Note: If you want to change the Color of other series column within this Column chart, same logic as the first item within above array.

 

If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support
Community Support

Re: Building a Chart based on Sharepoint Data and Drop Down Menus

Hi @LaingBennett ,

Do you want to filter your data displayed within the Chart control based on Department and Event Type?

 

I have made a test on my side, please consider take a try with the following workaround:

Add two Dropdown controls in your app -- Department Control & Event Type Control. Set the Items property of the Department Dropdown box to following:

["Lyminge", "Hythe", "Lettings"]

Set the Items property of the Event Dropdown box to following:

["Valuation", "Take On", "SSTS", "Let Agreed", "Exchange", "Viewings"]

 

Set the Items property of the Column Chart control to following (If the Department field and Event field are both Choice type field in your SP List😞

Filter(
       'LB - Intake Book Monthly Totals',
       Department.Value = DepartmentDropdown.Selected.Value,
       Event.Value = EventDropdown.Selected.Value
)

If the Department field and Event field are both Text type column, please consider modify your above formula as below:

Filter(
       'LB - Intake Book Monthly Totals',
       Department = DepartmentDropdown.Selected.Value,
       Event = EventDropdown.Selected.Value
)

 

If you want to slight gap between each pair of columns in your column chart control, please set the ItemsGap property of the Column chart to a proper value. If you want to change the color for each pair of columns in your column chart control, please consider change the color listed within the ItemColorSet property of the Column Chart control.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
LaingBennett Helper I
Helper I

Re: Building a Chart based on Sharepoint Data and Drop Down Menus

Hi there,

 

This has worked perfectly. 

 

Is there any way that I can change the colour of the chart based on the item selects in the Departement Dropdown menu? 

 

For example

Lyminge = Red

Hythe = Blue

Lettings = Yellow

 

This would be only for the Actual Data. The Target Data column would always remain in the same colour. 

Community Support
Community Support

Re: Building a Chart based on Sharepoint Data and Drop Down Menus

Hi @LaingBennett ,

Is the solution I provided above helpful in your scenario?

 

If you want to change the Color of the Column chart based on the selected Departement value, I think the ItemColorSet property of the Column chart and the If function could achieve your needs.

 

Please consider set the ItemColorSet property of the Column Chart to following formula:

[
   If(
       DepartmentDropdown.Selected.Value = "Lyminge", Color.Red, 
       DepartmentDropdown.Selected.Value ="Hythe", Color.Blue, 
       DepartmentDropdown.Selected.Value ="Lettings", Color.Yellow
   ),
   RGBA(48,166,103, 1), RGBA(94,193,108,1), RGBA(246,199,144,1), RGBA(247,199,114,1), RGBA(247,180,91,1), RGBA(246,143,100,1), RGBA(212,96,104,1), RGBA(148, 110, 176, 1), RGBA(118, 154, 204, 1), RGBA(96, 197, 234, 1)
]

Note: If you want to change the Color of other series column within this Column chart, same logic as the first item within above array.

 

If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (8,480)