Hello Power Apps gurus. I am in some desperate need of assistance. I have run into a situation that I can’t seem to find an answer to or not sure if it is even a possibility. I will try to explain this as best as I can.
My app has a gallery that pulls from a SharePoint List. Everything in my app works, but I wanted to provide my leadership a visual accounting of the work that is being done. Right now, I have a host of labels that contain formulas to calculate and filter the data that I need. Here is a current example.
Task By Category:
Category 1: 19
Category 2: 25
Category 3: 2
Category 4: 17
Each number is a label that is filtered individually with the formula noted below.
CountRows(Filter(Gallery9_2.AllItems, 'Department Column.Value <> "No", 'Category'.Value = "Category1"))
I was trying to put together a bar chart to visualize the data the formula below, but it only allows the one series. Therefore, the charts will show the four category labels, but the amounts are all for category 1 rather and each represented category (not sure how to pull number for each of the categories into the chart).
AddColumns(GroupBy(AddColumns(‘SP LIST NAME’, "Category1", 'Category'.Value),"Category1", "GroupData"), "Name", CountRows(Filter(Gallery9_2.AllItems, 'Department Column.Value <> "No", 'Category'.Value = "Category1"))
This is not the exact chart but an example of how the data is represented.
Is there a method or work around to represent this data in a chart. I would like to stay with the gallery to avoid any delegation issues as I would like the user to be able to filter the data by date for their analysis. Currently, the numbers are adjusted based on the filters the user inputs into the gallery.
Any guidance is greatly appreciated.
Hi ,
Im loading the Data's from SharePoint List into Column chart, It counts the Status value columns. Im using below Code in Item property, which works fine
ForAll(Choices('AOCC Task List'.Status) As A,{Number:CountIf(ForAll('AOCC Task List',ThisRecord.Status.Value) As B,B.Value=A.Value),Value:A.Value})
My requirement is, I want to filter the same but with current user with item created by.
Im unable to add filter for current user in the above comment.
Update: I just realized why it is captured that way. It isn't capturing because the list has zero events, there are list items that do not have a category designated. Thanks for your help.
@v-qiaqi-msft , I have a quick question. In my data set, there are 2 categories that have a zero count. The chart lumps them together as a number label (ex. [7]) and then counts the total number of events that had zero. See chart below. Is there a way that it shows the two individual categories with a zero sum?
@v-qiaqi-msft , that actually worked perfectly and provided exactly what I was looking for. Thank you so much. You have saved me so much time.
Hi @Anonymous,
I think there is something syntax wrong with your formula.
Your CountRows() function only count for the Category1 instead of all the other categories.
Could you please show me you original data or some sample data?
You should group by the whole Category column, and in the group table, there will be multi rows corresponding to each distinct category, then you should count rows for each group for corresponding category:
AddColumns(GroupBy(AddColumns(Filter(Gallery9_2.AllItems, 'Department Column.Value <> "No"), "Category1", 'Category'.Value),"Category1", "GroupData"), "Name", CountRows(GroupData))