Forum Discussion

awff's avatar
awff
Icon for Helper III rankHelper III
4 years ago
Solved

Averages not calculating correctly when grouped

Hi All, Happy Friday from Australia!

 

After wasting hours trying to figure this out, i'm reaching out for help!


I'm trying to work out the average days taken to invoice each quote on a group by quote level. Currently the -21.8 average for "Days Taken" is correct on a row by row basis in the below table:

 

AccountQuote NameStart DateEnd DateInvoice DateDays TakenProductTotal Price
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14A$460
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14C$760
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14B$1,899
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14C$3,039
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14C$1,140
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14C$25,369
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14D$3,039
Customer Pty ltdQuote B31-Aug-2030-Jun-2128-Oct-2058A$2,455
Customer Pty ltdQuote B30-Oct-2030-Jun-2128-Oct-20-2A$2,292
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41A$565
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41A$940
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41B$2,351
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41C$3,760
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41D$1,412
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41D$31,387
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41C$3,760
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41E$13,225
    PBI average result-21.8 Total: $97,851

 

However... i want to be able to group these by:
* Quote;
* Start Date;
* Remove Product, and only show Sum of all Total Price

 

...so there should be only four rows distiguised by the font colour and should look like this Quote B has different days and shows as two rows.

AccountQuote NameStart DateEnd DateInvoice DateDays TakenTotal Price
Customer Pty ltdQuote A30-Jun-2030-Jun-2116-Jun-20-14$35,704
Customer Pty ltdQuote B31-Aug-2030-Jun-2128-Oct-2058$2,455
Customer Pty ltdQuote B30-Oct-2030-Jun-2128-Oct-20-2$2,292
Customer Pty ltdQuote C30-Jun-2130-Jun-2220-May-21-41$57,400
    PBI average result-21.8Total: $97,851

 

THE ISSUE: The average is incorrect and is still calculating on a row by row basis. The desired result would be 0.25.

 

Any help would be greatly appreciated!

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi awff ,

     

    Please try the following formula:

     

    Measure = 
AVERAGEX (
    SUMMARIZE (
        'Table',
        'Table'[Quote Name],
        'Table'[Start Date],
        "Average of Days", AVERAGE ( 'Table'[Days Taken] )
    ),
    [Average of Days]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi awff ,

     

    Please try the following formula:

     

    Measure = 
AVERAGEX (
    SUMMARIZE (
        'Table',
        'Table'[Quote Name],
        'Table'[Start Date],
        "Average of Days", AVERAGE ( 'Table'[Days Taken] )
    ),
    [Average of Days]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.