Create table/chart with columns in PowerApps

(0) Share

Report

Posted on by RVoller

194

Hi,

I am building a table that I can use to create a chart in PowerApps.

I need to:

Filter the collection according to a CR (Customer Request Type).

The CR must have a status of "Complete"

I then need a DaysClose column that will create a column to show the total number of days for the request to close (This is a DateDiff calcuation comparing the Completion Date with the Created Date).

I also need the AvgDay column. This will just be an average of the datediff for the filter.

I cannot get 'DaysClose' and 'AvgDay' to work. Any assistance would be appreciated.

The code is as follows:

ClearCollect(colTest,
    Table(
        { 
         Type: "Additional Direct Debit",  
         Total:Sum(ForAll(Filter(colReport, CR="Additional Direct Debit"),1),Value),
         Completed:Sum(ForAll(Filter(colReport,CR="Additional Direct Debit" && Status = "Complete"),1),Value),
         DaysClose:Sum(ForAll(Filter(colReport,CR="Additional Direct Debit" && Status = "Complete"), DateDiff('Completion Date', Created),Value),
         AvgDay:Average(ForAll(Filter(colReport,CR="Additional Direct Debit" && Status = "Complete" ), DateDiff('Completion Date', Created)  1),   Value)}))
 
 
Kind regards
 
Rene Voller

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @RVoller, can you tell what kind of chart you want to work with.based on that we can assist you with the same

Was this reply helpful? Yes No
RVoller 194 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Just a pie chart or a bar chart - i.e. the standard reports in PowerApps.

Thanks for the reply. Appreciated.

Kind regards

Was this reply helpful? Yes No
Verified answer

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi ,
Can you try this formula :
/* Collect a single-record table summarizing “Additional Direct Debit” metrics */ ClearCollect( colTest, Table( { // 1. Label for this row Type: "Additional Direct Debit", // 2. Total number of requests of this type Total: CountRows( Filter( colReport, CR = "Additional Direct Debit" ) ), // 3. Number of completed requests Completed: CountRows( Filter( colReport, CR = "Additional Direct Debit" && Status = "Complete" ) ), // 4. Sum of days to close — traps invalid dates per record DaysClose: Sum( Filter( colReport, CR = "Additional Direct Debit" && Status = "Complete" && ! IsBlank( Created ) && ! IsBlank( 'Completion Date' ) ), /* For each record, compute days difference; on error return 0 */ IfError( DateDiff( Created, 'Completion Date', TimeUnit.Days ), 0 ) ), // 5. Average days to close — safe divide-by-zero handling AvgDay: IfError( Average( Filter( colReport, CR = "Additional Direct Debit" && Status = "Complete" && ! IsBlank( Created ) && ! IsBlank( 'Completion Date' ) ), DateDiff( Created, 'Completion Date', TimeUnit.Days ) ), 0 ) } ) )

Was this reply helpful? Yes No
Suggested answer

Inogic 1,265 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

You can try using the following formula to calculate the totals, completed count, days to close, and average days to close for the "Additional Direct Debit" type:

ClearCollect(
    colTest,
    Table(
        {
            Type: "Additional Direct Debit",
            Total: CountRows(Filter(colReport, CR = "Additional Direct Debit")),
            Completed: CountRows(Filter(colReport, CR = "Additional Direct Debit" && Status = "Complete")),
            DaysClose: Sum(
                Filter(colReport, CR = "Additional Direct Debit" && Status = "Complete"),
                Round('Completion Date' - Created, 0)
            ),
            AvgDay: Round(
                Average(
                    Filter(colReport, CR = "Additional Direct Debit" && Status = "Complete"),
                    'Completion Date' - Created
                ),
                0
            )
        }
    )
)

This formula collects summary metrics into a collection named colTest, which you can then use for reporting or visualization.

Hope, this helps.

Thanks!

Inogic

Was this reply helpful? Yes No
RVoller 194 on at

Like (0)

Report
Copy link

Link copied!

Thank you for the replies. Chart is working as expected.

Kind regards

Rene Voller

Was this reply helpful? Yes No