Answered

Building a Chart based on Sharepoint Data and Drop Down Menus

(0) Share

Report

Posted on by LaingBennett

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

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
LaingBennett 84 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No