Pie Chart on a SharePoint Choice Field

[BobK]

I want to make a Pie Chart from a SharePoint List. I can do it with the following:

AddColumns(GroupBy(ClientIntakeList_1, "Office_name", "GPOffice"), "CountbyOffice", CountRows(GPOffice))

Where I have a simple text field for offices.

But if I change the text field to a choice field to force one of three choices, I receive an error.

I found I can use groupby to group on choice field:

Groupby(AddColumns(DataSourceName, "NewFieldName",Category.Value),"NewFieldName","GroupName")

But when I combine the two I get an error

AddColumns(GroupBy(AddColumns(ClientIntakeList_1, "NewOfficeField", Office_Name.Value) "NewOfficeField", "GPOffice"), "CountbyOffice", CountRows(GPOffice))

Any suggestions?

Thanks for any help!

[v-xida-msft · Accepted solution]

Hi @BobK,

Could you please share more details about the error message?

Could you please share a bit more about the data structure of your SP list?

I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:

Set the Items property of the PieChart control to following:

AddColumns(
      GroupBy(
           AddColumns('20190102_case6',"NewFieldName",Office_Name.Value),
           "NewFieldName",
           "GroupData"
      ),
      "CountByOffice",
      CountRows(GroupData)
)

Based on the formula you provided, I think there is something wrong with its syntax -- You have missed a comma within your GroupBy formula. The screenshot as below:

Please modify your formula as below:

AddColumns(
      GroupBy(
           AddColumns(ClientIntakeList_1, "NewOfficeField", Office_Name.Value),  /* <- Add comma here */
           "NewOfficeField", 
           "GPOffice"
      ), 
     "CountbyOffice", 
     CountRows(GPOffice)
)

If the issue still exists, please consider take a try to re-create a app on your side, and try above formula I provided again, check if the issue is solved.

Best regards,

Kris

[BobK]

That worked - Thank you!  I was missing that comma!

[BT168]

Hi @BobK @v-xida-msft 

I've made a pie chart from a choice column using your formula above (thank you so much for sharing that, was exactly what I needed!) However, I am looking to filter the data by another choice column in my SP list called 'Campaign'. 

Could you advise how to merge the 2 below together (if that's the right way of filtering by Campaign)? 

Current pie chart formula:

AddColumns(GroupBy(AddColumns(MilesChallenge,"TeamSumField", Team.Value), "TeamSumField", "TeamSum"), "TeamTotal", Sum(TeamSum,Miles))

Want to add:

(Filter(MilesChallenge,Campaign.Value = "Miles for Meals 2020")

Thank you!