Averages not calculating correctly when grouped

[awff]

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:

Account	Quote Name	Start Date	End Date	Invoice Date	Days Taken	Product	Total Price
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	A	$460
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	C	$760
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	B	$1,899
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	C	$3,039
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	C	$1,140
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	C	$25,369
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	D	$3,039
Customer Pty ltd	Quote B	31-Aug-20	30-Jun-21	28-Oct-20	58	A	$2,455
Customer Pty ltd	Quote B	30-Oct-20	30-Jun-21	28-Oct-20	-2	A	$2,292
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	A	$565
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	A	$940
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	B	$2,351
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	C	$3,760
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	D	$1,412
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	D	$31,387
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	C	$3,760
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	E	$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.

Account	Quote Name	Start Date	End Date	Invoice Date	Days Taken	Total Price
Customer Pty ltd	Quote A	30-Jun-20	30-Jun-21	16-Jun-20	-14	$35,704
Customer Pty ltd	Quote B	31-Aug-20	30-Jun-21	28-Oct-20	58	$2,455
Customer Pty ltd	Quote B	30-Oct-20	30-Jun-21	28-Oct-20	-2	$2,292
Customer Pty ltd	Quote C	30-Jun-21	30-Jun-22	20-May-21	-41	$57,400
				PBI average result	-21.8	Total: $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!

[v-kkf-msft · Accepted solution]

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.

[VahidDM]

Hi @awff 

Read this post, you can find your solution:
https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/