Building a Chart based on Sharepoint Data and Drop Down Menus

[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.

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.

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. 

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

[v-xida-msft]

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,

[LaingBennett]

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. 

[v-xida-msft · Accepted solution]

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,