Power Platform Community Forum Thread Details

Hi @psrini20

I tried creating a flow which will help you. In the sample flow, I have created a SharePoint list 'HelpDeskTickets' with two columns 'Department' (single line of text) and 'Status' (choice) fields:

Here is the flow:

1. Firstly, we will create a recurrence flow and set the frequency for it:

You may choose different trigger point as per your need.

2. Next, we will initialize 4 variables to store the values of Department, count for New/In progress/Completed tickets count:

3. Now, we will fetch the records or list items from SharePoint list using 'Get items' action:

You may write filter query to fetch past month data and so on.

4. We will use 'Select' action to get the value of Department field in a collection from the output of 'Get items' action. Click on 'Switch to text mode' and select Department field from Dynamic content window:

5. We will find the distinct Department using union() formula and then iterate the distinct Department items using 'Apply to each' action:

Expression used in above screenshot:

union(body('Select'),body('Select'))

We will store the value of each iterated department in 'Compose' action using expression as shown below:

Expression used in above screenshot:

item()

Continue inside 'Apply to each', now add 3 parallel action. In each action, we will count the no of tickets for each category (New, In progress & Completed) and overall total count for each category:

Branch 1 (New):

Using 'Filter array' action, we will filter the records on the basis of Department & status. Additionally, we will update the overall count variable for New tickets category:

To add filter expression, click on 'Edit on advanced mode' link at the bottom & add the below expression:

@and(equals(item()?['Department'], outputs('Compose')),equals(item()?['Status/Value'],'New'))

Update the overall New category ticket count using 'Increment variable' action:

Expression used for finding the count is:

length(body('Filter_array'))

Branch 2 (In Progress):

Using 'Filter array' action, we will filter the records on the basis of Department & status. Additionally, we will update the overall count variable for 'In progress' tickets category:

To add filter expression, click on 'Edit on advanced mode' link at the bottom & add the below expression:

@and(equals(item()?['Department'], outputs('Compose')),equals(item()?['Status/Value'],'In progress'))

Update the overall 'In Progress' category ticket count using 'Increment variable' action:

Expression used for finding the count is:

length(body('Filter_array_2'))

Branch 3 (Completed):

Using 'Filter array' action, we will filter the records on the basis of Department & status. Additionally, we will update the overall count variable for Completed tickets category:

To add filter expression, click on 'Edit on advanced mode' link at the bottom & add the below expression:

@and(equals(item()?['Department'], outputs('Compose')),equals(item()?['Status/Value'],'Completed'))

Update the overall Completed category ticket count using 'Increment variable' action:

Expression used for finding the count is:

length(body('Filter_array_3'))

Inside 'Apply to each' action, now add 'Append to array' action to save the record for each department:

Following expression is added inside 'Append to array variable' action:

{
"Department": @{outputs('Compose')},
"New": @{length(body('Filter_array'))},
"InProgress": @{length(body('Filter_array_2'))},
"Completed": @{length(body('Filter_array_3'))}
}

you may just copy & paste the above expression in your flow.

The structure of 'Apply to each' action will look like this:

6. After closing 'Apply to each' action, we will update the overall counts for all categories and append to the array. This action should be called outside the 'Apply to each' action:

Expression used in above screenshot is:

{
"Department": "Total",
"New": @{variables('varNewTicketCount')},
"InProgress": @{variables('varInProgressTicketCount')},
"Completed": @{variables('varCompletedCount')}
}

7. Finally, we will create an html table from the DepartmentStats variable using 'Create HTML table' action: