Dropdown menu for pie chart

[ShadowTech]

Hello,

Looking to create a pie chart with a dropdown menu that users can see different data in the pie chart based on the dropdown menu selections which include by "current year" and "current month" of the column called "Status". The column  is a "Choice" SharePoint column. In addition to the SharePoint column I need to see the total count of each value.

Example below:

Active 10
Waiting Approval 5
Complete 6
Not Accepted 3

Thanks in advance.

[ShadowTech]

@RandyHayes Yeah I found that! I see everything now just no count next to the value in the legend.

example: Active 12. Just shows the value "Active".

[RandyHayes]

@ShadowTech 

Yes, the piechart does not have counts to it.  The Legend utilizes the labels for the series of the pie chart.

So, you need to do this on the series labels.  

ForAll(
    SortByColumns(
        GroupBy(
            AddColumns(
                Filter(yourSharePointList, (Created>=yourDropdown.Selected.start) && (Created<=yourDropdown.Selected.end)),                
                "_status", Status.Value
            ), 
            "_status", "records"
         ), 
         "_status"
     ),
     {Value: Coalesce(_status, "Blank") & " " & CountRows(Filter(records, Status.Value=_status), 
      Count:CountRows(Filter(records, Status.Value=_status))
     }  
)

However, this will give you counts on the pie and in the legend. 

[ShadowTech]

@RandyHayes So let me ask you this then. Maybe it make sense to have a separate label and will have those counts in conjunction with the dropdown? I'm ok with a separate label with those values.

Thoughts? Again thanks for all your help.

[RandyHayes · Accepted solution]

@ShadowTech 

Yes, but there is a trick to this.  One involves copying and pasting your Items formula for the PieChart - which I definitely hate to do (OHIO - Only Handle Information Once).

So, the method I use is to add a dynamic table variable to the app.  What is a dynamic table variable you might ask...well, I'll tell you the steps:

1) Add a new scrollable screen to your app. (we will be deleting this screen shortly)

2) On that screen, select the Canvas that it has (let's say it is Canvas1) and resize it, especially drag the top of the canvas down to a new Y location (this breaks some of the other formulas that get generated on that scrollable screen - we want them to break).  You can rename this canvas if you like.

3) In that canvas, there will be a DataCard (let's say it is DataCard1).  Let's rename that to dcdPieData

4) Make that datacard small - it will not contain anything in it so the smaller the better.  Do the same with the Canvas size...make it small.

5) Select the Canvas and Cut it (ctrl-X) from the screen.

6) Select the new screen you created and Delete it

7) go back to your PieChart screen and paste (ctrl-V) your canvas to that screen.  At this point you can also set the Visible property of the Canvas to false

😎 Go into the properties of the dcdPieData datacard and set the Update property to the following:

{PieData:
    ForAll(
        SortByColumns(
            GroupBy(
                AddColumns(
                    Filter(yourSharePointList, (Created>=yourDropdown.Selected.start) && (Created<=yourDropdown.Selected.end)),                
                    "_status", Status.Value
                ), 
                "_status", "records"
             ), 
             "_status"
         ),
         {Value: Coalesce(_status, "Blank"), 
          Count:CountRows(Filter(records, Status.Value=_status)),
          Legend: Coalesce(_status, "Blank") & " " & CountRows(Filter(records, Status.Value=_status)
         }  
    )
}

9) Now change the Items property of your PieChart to the following: dcdPieData.Update.PieData

10) Change the Items property of the Legend to : dcdPieData.Update.PieData.Legend

Now, this will all change dynamically based on your dropdown selection.  Your pie chart will have name only labels and your legend will have name and count.

[ShadowTech]

@RandyHayes looks as if it doesn't like the formula:

Canvas data card - Doesn't like the curly braces 

Error on Legend: 

Error on the pie chart:

[RandyHayes]

@ShadowTech 

Looks like I was missing a paren in the formula:

{PieData:
    ForAll(
        SortByColumns(
            GroupBy(
                AddColumns(
                    Filter(yourSharePointList, (Created>=yourDropdown.Selected.start) && (Created<=yourDropdown.Selected.end)),                
                    "_status", Status.Value
                ), 
                "_status", "records"
             ), 
             "_status"
         ),
         {Value: Coalesce(_status, "Blank"), 
          Count:CountRows(Filter(records, Status.Value=_status)),
          Legend: Coalesce(_status, "Blank") & " " & CountRows(Filter(records, Status.Value=_status))
         }  
    )
}

[ShadowTech]

@RandyHayes Yeah I tried that but still throws out an error.

[RandyHayes]

@ShadowTech 

I just double checked and even threw it into a formula editor and it all looks good.

You are putting this in the Update property of the datacard, correct?

Send me a screenshot of the entire formula so that I can look closer.

[ShadowTech]

@RandyHayes I fix it. Missing part of the formula. Only thing now is the Legend only shows the colors no text.

[RandyHayes]

@ShadowTech 

What is the Items property of the Legend control?