cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShadowTech
Post Prodigy
Post Prodigy

Dropdown menu for pie chart

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:

 

pie.jpg

 

 

Active 10
Waiting Approval 5
Complete 6
Not Accepted 3

 

 

 

 

 

dropdown.jpg

 

 

 

Thanks in advance.

34 REPLIES 34

@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
Super User III
Super User III

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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
Super User III
Super User III

@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.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

 

Canvas data card - Doesn't like the curly braces 

 

curly.jpg

 

 

 

 

 

Error on Legend: 

 

bad1.jpg

 

Error on the pie chart:

 

bad2.jpg

RandyHayes
Super User III
Super User III

@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))
         }  
    )
}
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

 

bad 3.jpg

RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

 

legend.jpg

RandyHayes
Super User III
Super User III

@ShadowTech 

What is the Items property of the Legend control?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,027)