Connect pie chart to SharePoint list not showing "Choice" columns

[ShadowTech]

Hello,

I'm connecting a pie chart to a SharePoint list but is not giving me the ability to choose any "Choice" Columns. Will the pie chart even recognize them? How do I get the pie chart to accept those columns?

Thanks in advance.

[ShadowTech]

@rebeccas This is what I get:

Plus getting errors.

ClearCollect(CollInvCorrCount, AddColumns(GroupBy('Credit Memo', "Invoice Correction","GrpByCount"),"CountCorrection",CountRows(GrpByCorrection)))

Not seeing additional choices in the "Series".

[rebeccas]

Hmm...your series should have the option of "Invoice Correction" and the Series should have "CountCorrection" as an option. 

Do you have any errors on the collection? Also, check to see if the collection gathered the data as it should..you can drop in a quick gallery to see what it shows or click View/Collections and select CollInvCorrCount and see if it list any items. What is the column type for "Invoice Correction"? Is it single line text or choice? 

[ShadowTech]

@rebeccas This is what I'm seeing.

Looks as if its not recognizing the column. It is a choice column.

[rebeccas]

If you have your column as a choice column you might have to do this instead:

ClearCollect(CollInvCorrCount1,GroupBy(AddColumns('Credit Memo', "Inv Correct Txt",'Invoice Correction'.Value),"Inv Correct Txt","GrpByJobCorrection"));
ClearCollect(CollInvCorrCount2, AddColumns(GroupBy(CollInvCorrCount1,"Inv Correct Txt","GrpByJobCorrection2"), "CountStatus",CountRows(GrpByJobCorrection2)))

CollInvCorrCount2 is what you will use as your Items instead.

[ShadowTech]

@rebeccas Better but its dividing the chart evenly when the count attached to the values is not even. For example there are two instances of the value Quantity and only one for N/A and Customer No..

Basically how many time a value is used should give them that much more of a piece of the pie.

[RandyHayes]

@ShadowTech 

Sorry had to step away for a while.  

So in your image, you are using the ID as your series.  I believe you are looking for a count.

Your formula on the Items property of your Pie Chart should be this if you are trying to "pie" the quantity of each choice value.

ForAll(
   GroupBy(
       AddColumns('Credit Memo', 
           "_invCorrect", 'Invoice Correction'.Value
       ),
       "_invCorrect",
       "_records"
   ),
   {Value: _invCorrect, Count: CountRows(Filter(_records, 'Invoice Correction'.Value = _invCorrect))}
)

[ShadowTech]

@RandyHayes Looks as if its working now. Awesome! Thanks Randy.

Will this same formula work for the Column and line charts? Thanks again.

[RandyHayes]

@ShadowTech 

So based on the formula that I provided you ALL of the "slices" you see are coming from the Choices in your Choice column.  So, if you are seeing multiple Quantity or other duplicates, then there is an issue in your Choice column.

Can you expand on how that Choice Column is defined?  Is it a Choice column with Multiple Selections allows (CheckBox) or one that allows write-ins?

[ShadowTech]

@RandyHayes  No actually its working fine now. I had to edit my reply back to you.

I did have a question: Will this same formula work for the Column and line charts?

Thanks again.

[RandyHayes]

@ShadowTech 

Short answer is yes...but typically you are representing different aspects of data in a Column or Line Chart, so you might want to adjust as needed.

The key to charts is to provide a formula in your Items property that has your data "Shaped" the way that you want it to be used in the chart.  

I would advise being familiar with Groupby, AddColumns, and a few others - and most importantly, knowing that these functions all return tables of your data adjusted based on the formula you use.  Example, if you use AddColumns, the result of that function is a Table with the column added.  You can "feed" that to the datasource of another function and alter more and so forth.

It is also important to get used to ForAll - not as a For Loop like so many think it is, but as an "iterator" that will allow you do return any kind of record you want.

In the formula I provided (working in to out), we Added a column to the 'Credit Memo' table that added a text element to the records called _invCorrect.  We then assigned the value of the choice column to that element.

That AddColumns function will be returning a table with records from the original source and the added column element.

We then used the Groupby function to group the records of that returned table.  We grouped by the _invCorrect column.  The Groupby function at this point will be returning a table with one column called _invCorrect (the column we grouped by) that will have a Distinct list of all the values from the inner table.  It will also return with it a column called "_records" which will be all of the records that match the _invCorrect value.

NOW...the Groupby is returning a table, that table is being "fed" to the ForAll.  ForAll will iterate over all of the rows in that table (there will be one row for each _invCorrect, as mentioned above).  SO...that is the table datasource for the ForAll.  In the ForAll, we then create a record (the curly bracket nomenclature) for each iteration.  And in that record, we define a Value column, which we assign the _invCorrect value and then a Count column where we count the number of records that match that status in the _records table.

End result is that we get a completely different table to feed to the chart.  But, that table has the exact "shaped" data that we want to chart.  By this stage of the formula...the final table...there really isn't anything else from the original datasource in it, only shaped information about it.

Hopefully that is fairly clear and will give you some information to move forward and not only chart to your heart's content, but also to use this in many other places.

Note: you can uses these formulas completely within the items property, there is no need for collections or other variables to make it happen.  And, the added advantage of NOT using collections or variables is that your chart will be dynamic based on the underlying datasource...if you change it with an update or patch or someone else does and you refresh, your chart will also change immediately.  No need to go back and forth between screens to get it to update or press other buttons to refresh....it will all be automatic!

Enjoy!