Multiple colums in Pie Chart from SharePoint List

[SamSmithIPU]

Hi all,

I am trying to create a Pie Chart using data from a SharePoimt list.  I want to use 3 columns as my data for the chart but I can only pick one.

In my list I have a text column filled in by Month/year (I.e Jan-2021), from a Dropbox choosing the Month/Year the PieChart will then be filtered by that row of thr list.  I can get this part working fine.

The next part is where I am struggling, I want the pie chart to represent values from 3 different number columns.  For example for the row with the column Jan-2021 contains Column 1 with the figure 8, Column 2 has 4 and Column 3 has 12.

Can anyone help please?  If more info is needed just ask

[RandyHayes]

@SamSmithIPU 

You will need to get your Items property of the Pie chart to the following structure:

Table(
    {Column1: 8},
    {Column2: 4},
    {Column3: 12}
)

This is typically done in the following way:

With({_items: Filter(yourList, yourCriteria)},
    {
        Column1: Sum(_items, Column1),
        Column2: Sum(_items, Column2),
        Column3: Sum(_items, Column3)
    }
)
    

I hope this is helpful for you.

[SamSmithIPU]

Hi @RandyHayes

I have copied that and made modifications but I am seeing the below error

To help understand further, this is the modifications and what I am after:

Modified Code:

With({_items: Filter('Workshop KPI''s', 'Month Selection - M2M')},

{

Column1: Sum(_items, EngineeringRework),

Column2: Sum(_items, SupplierRework),

Column3: Sum(_items, WorkshopRework)

}

)

'Month Selection - M2M' is the name of the Dropdown

SharePoint List, please see attached

Chart End Goal, please see attached

The Chart Goal screenshot is what is currently being used in an Excel File for comparison of what I want in PowerApps

[RandyHayes]

@SamSmithIPU 

Yes, your criteria of the Filter must be evaluate to a Boolean true or false.  I didn't specify the criteria because you mentioned that you already had that part working.

So for your Filter, you need to compare your dropdown to the column in the list. 

I would need to know the exact Items property of the dropdown to give an exact formula, but the basic idea is:

With({_items: Filter('Workshop KPI''s', Month='Month Selection - M2M'.Selected.Value)},
    { 
       Column1: Sum(_items, EngineeringRework),
       Column2: Sum(_items, SupplierRework),
       Column3: Sum(_items, WorkshopRework)
    }
)

[SamSmithIPU]

Hi @RandyHayes ,

Made a small change, Selected.Value is now Selected.Month.  Small oversight like when you misplace a full stop.

The chart pulling data, but it is essentially where it was in the beginning without any formulas.  The Image below is only showing one column's worth of data, I'm not sure though as the label only says Item(1)

For both dropdowns I see the below, is there a way to have all 3 columns grouped together as a selection but values still different so when they are displayed they are not all summed together and will show individually?

[RandyHayes · Accepted solution]

@SamSmithIPU 

Duh...hand smack to my head...my bad!

I apologize, I left out a couple of important parts.  I either had one too many, or too long a day.

Here is the formula you need:

With({_items: Filter('Workshop KPI''s', Month='Month Selection - M2M'.Selected.Value)},
    Table(
         {label:"Engineering Rework", Value: Sum(_items, EngineeringRework)},
         {label:"Supplier Rework", Value: Sum(_items, SupplierRework)},
         {label:"Workshop Rework", Value: Sum(_items, WorkshopRework)}
    )
)

As you can see...vastly different.  I have no idea what I was thinking on my last reply.

Throw that formula in and see what you get.  You should be able to select label for your Labels and Value for your series in the chart.

[SamSmithIPU]

@RandyHayes You absolute Legend!!!

I just had to make one word change and this worked, I changed Month='Month Selection - M2M'.Selected.Value to Month='Month Selection - M2M'.Selected.Month

With({_items: Filter('Workshop KPI''s', Month='Month Selection - M2M'.Selected.Month)},
    Table(
         {label:"Engineering Rework", Value: Sum(_items, EngineeringRework)},
         {label:"Supplier Rework", Value: Sum(_items, SupplierRework)},
         {label:"Workshop Rework", Value: Sum(_items, WorkshopRework)}
    )
)

[RandyHayes]

@SamSmithIPU 

Ooops, I do recall you mentioned that change before.  I should have incorporated it in.

But...glad you got it working now.