How to create pie chart from sharepoint list using groupby and add column

[lmcc]

Hi,

Here is my sharepoint list. 

I want to produce a pie chart that shows the total count of ACTIVE and INACTIVE in the status column of a sharepoint list.

I am unsure of the formula in the items property. I see some that use an ADD COLUMN and then a GROUPBY. Others do it the other way around. 

Here is a screenshot of my sharepoint list and powerapp page.

Thank you in advance. 

[SpongYe]

Hi @lmcc 

Its looks like something is wrong with your datasource. 

I created a simple piechart and visualize the output and everything worked. 

Can you make a screenshot of your datascource like the screen below of your pie.Items:

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[rubin_boer]

hi  @lmcc welcome to the community.

In your case you would like to display the pie chart based on Grouped Status Column.

Steps.

Group your data by the column for example: GroupBy(colYourData, "Status", "Statuses"), this will give you a new table with the number of unique values you have in the Status column, and the rest of the columns will be tables.

Now you will need to get the totals and to do that you add a column to count the "Statuses" tables for each row. this will be done in your Items of the Pie Chart

Consider the data source: yourDataSource

Pie Chart Items = AddColumns(GroupBy(colYourData, "Status", "Statuses"),"Totals",CountRows(Statuses))

Result:

[lmcc]

@rubin_boer ,

Hi there, 

I think you're close to helping me find a solution. However, it is currently not working. Perhaps because the column in the sharepoint list is a choice column. 

How would this change the formula?

Thank you,

[rubin_boer · Accepted solution]

hi @lmcc 

here you go for a choice column. get the value from the complex type and group it.

AddColumns(
    GroupBy(
        AddColumns(
            OppsAgain,
            "yourNewStatus",
            Status.Value //Status is your choice column
        ),
        "yourNewStatus",
        "Statuses"
    ),
    "Totals",
    CountRows(Statuses)
)

Result

[csuk]

Hi @rubin_boer 

Taking this little further, if I want add total count, for example Active(6), Inactive(4), to labels on pie chart, how I can add that?

[ecamar13]

 Hi Rubin, quick clarification
 
"OppsAgain" what is this ? random table name ?
"yourNewStatus" What is this ? Sharepoint list name ?
"Status" Sharepoint column name
"Statuses" new column table ?

please clarify,thank you for your help

[rubin_boer]

hi   @ecamar13 

There are 3 steps in this formula:

Step 1

Add a column to the datasource (OppsAgain) and call this column "yourNewStatus" with a value Status.Value (SharePoint Column: Status)

AddColumns(
            OppsAgain,
            "yourNewStatus",
            Status.Value //Status is your choice column
        )

The output is having an additional column added your datasource called yourNewStatus with a value from SharePoint Status for the record assigned to it.

Step 2

Group the datasource by the column added and name the group "Statusses" 

Step 3 

Add a column to the table created by Group By, this is the new column: "Totals" 

Please find the following:

"OppsAgain" what is this ? random table name ? -

The datasource/name of your SharePoint List
"yourNewStatus" What is this ? Sharepoint list name ? -

A column added to your datasource
"Status" Sharepoint column name -

Yes the name of the column in SharePoint
"Statuses" new column table ?

No this is the name of the column(s) grouped. i can see why you want to refer to it as a column as that is how it will be displayed, but technically see it as a grouped name rather column name.

[ecamar13]

thank you for all support. I want to create a pie chart for OEM Status

I added a column "yourNewStatus", what is the column format and how to add a value OEM Status.Value (SharePoint Column: OEM Status)

[ecamar13]

Please see question below.