How to lookup Pie Chart slice details

[fkich1977]

Hi everyone,

First of all, I'd like to say that this forum is an invaluable help to all of us that are struggling to learn PowerApps, as I could find great explanations that helped me overcome several hurdles, but I'm now posting my first question since I couldn't find anything yet to resolve this. I've attached an image with the screens and I'll be detailing the issues as best as possible.

I'm creating an app to keep track of hardware (computer) refresh for 2021, my data source is a SharePoint List with columns like country, employee name, device type, make, model, profile (for new hardware bundle), scheduled month for the refresh and status (not started, in progress, complete). 

First, just a minor gripe - In the first screen, everything is working fine except for the Reset button which I'd like to use to reset all three filter options (the dropdown and the 2 text inputs). For now, it works for dropdown, and I have the Reset property on the text inputs so I can delete the contents by clicking on the "X" that shows up in the right side of the input. I've seen comments about setting global variables and just putting all 3 Resets in a row - Reset(control1); Reset(Control2); Reset(Control3); - but none actually worked better than what I currently have in place. So, suggestions to improve on this are welcome.

The real issue is with the pie chart in the second screen. After learning I can't make the chart labels show values like counts or percentages, I came up with a legend composed of rectangles for the colors, labels for the series and labels that receive the percentage value from a formula, which works great, except if there's no occurences for that status, no value is displayed along the % sign:

Text(
    LookUp(
        AddColumns(
            GroupBy(
                Filter(
                    LATAMCollection;
                    Title = Dropdown1.SelectedText.Result
                );
                "Replacement_x0020_Status";
                "GroupData"
            );
            "Percentage";
            CountRows(GroupData) / CountIf(
                LATAMCollection;
                Title = Dropdown1.SelectedText.Result
            )
        );
        Replacement_x0020_Status = "Not Started";
        Percentage
    ) * 100;
    "[$-en-US]#"
) & "%"

I've tried to add an If() statement where if there's no value, just write a 0, but that never worked.

The second thing I'd like to implement is a sort of lookup table that, when I select a pie chart slice, a smaller table on the side populates with the employee and manager names, so I know that for some country, there's those people that we haven't started refreshing the hardware, for example. It's not mandatory, but just something that would be cool to have. I believe I need to use lookup functions, but since I'm adding columns to a group when updating the chart, can I lookup that information? I couldn't find a way to do so.

So, after some 2 weeks of intensive learning, reading, testing, making progress, getting stuck, frustrated, making progress again, and all that, that's where I'm at currently, and so any help with those questions would be awesome.

Thanks guys, and wish you all a merry christmas, in the meantime. Stay safe.

[WarrenBelz]

Hi @fkich1977 ,

For the first one (and I cannot see your model, so this is just some syntax thoughts

With(
   {
      wLATAM:
      Filter(	  
	     LATAMCollection;
         Title = Dropdown1.Selected.Result &&
		 Replacement_x0020_Status = "Not Started"
      )
   };
   Text(
      AddColumns(
         GroupBy(
            wLATAM;
            "Replacement_x0020_Status";
            "GroupData"
         );
         "Percentage";
         CountRows(wLATAM) / CountRows(GroupData) *100
      ); 
      "[$-en-US]#"
   ) & "%"
)

On the second one, you can do that with an embedded PowerBI tile, but I do not believe PA Graphs

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[RandyHayes]

@fkich1977 

Chiming in on #2 - yes, the PowerApp pie chart does have a Selected property.  

You can base a Gallery/Table off of the Selected property.  The property will be a record and it will contain all of the columns of the record from which the pie chart slice is made of.

I hope this is helpful for you.

[fkich1977]

Hi @WarrenBelz

Sorry for not replying earlier. Thanks for the tip, I've tested your suggestion and the percentage value for "empty" series is just blank, while before it was just the % sign. What I'm trying to do is for the percentage to show as 0%. Please let me know what other information you might need from my app to help you understand what I'm working with. Thanks.

[fkich1977]

Hi @RandyHayes

Sorry for not replying earlier. That was helpful, yes, so I entered "piechart.selected" in the data table Items property and the fields used to populate the pie chart are now available. The thing is that I'm only grouping by Replacement Status to obtain the percentage, so I don't currently have the information to display in the table (employee and manager names). So the next question is, since I'm simply grouping by Replacement Statuses in a GroupData table I don't think I can add the names to the same table. Can I lookup to the my original collection and pull that information to display in the table? Thanks.

[WarrenBelz]

Hi @fkich1977 ,

Looking back (I simply gave you alternate syntax for your logic), this will not work as you only have one group (it is grouped by the same thing as you have filtered out). To get the percentage of records of the Title selected that are "not Started". as a percentage of those with just the Title selected, this may be all you need.

With(                          
   {
      wNoStart:
      Filter(	  
         LATAMCollection;
         Title = Dropdown1.Selected.Result &&
         Replacement_x0020_Status = "Not Started"
      ); 
      wTitle:
      Filter(	  
         LATAMCollection;
         Title = Dropdown1.Selected.Result
      )
   };
   Text(
      CountRows(wNoStart) / CountRows(wTitle) * 100
      ); 
      "[$-en-US]#"
   )& "%"
)

Also on the last one, I forgot to add .Percentage at the end of the Text part (I it should have resulted in 100% as noted above (dangers of free-typing).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @fkich1977 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.