Average of Average

[C4178756]

I have created an average of a process score by week which is correct and shows in the following graph.

When I put the measure into a table with total or card I get the average as 93.83 which is incorrect as what I need is the average taking from each week.  The cumulative is calculated over 21 weeks.  So is correct in excel as 94.03.  I've attached part of the table at the bottom of the thread. If more is required let me know and I will try to post.

Week	PF Average	Cumlative Average
16	91.09	94.42
17	89.33	94.12
18	93.02	94.06
19	91.66	93.93
20	93.66	93.92
21	96.2	94.03

I've tried the following DAX codes but had no success as they all return 93.83.

Average Value =

VAR _AVERAGE =

    AVERAGEX (

        VALUES ( 'QA Process Form'[Week] ),

        CALCULATE ( AVERAGE ( 'QA Process Form'[PF Score] ) )

    )

VAR _AVERAGEALL =

    AVERAGEX (

        SUMMARIZE ( ALL ( 'QA Process Form'[Week] ), 'QA Process Form'[Week] ),

        CALCULATE ( AVERAGE ( 'QA Process Form'[PF Score] ) )

    )

RETURN

    IF ( ISINSCOPE ( 'QA Process Form'[Week] ), _AVERAGE, _AVERAGEALL )

AND

Number of Sales =

AVERAGEX (

    DISTINCT ( 'QA Process Form'[Week] ),

    CALCULATE ( COUNT ( 'QA Process Form'[PF Score] ) )

)

Date	PF Score	Week	Year
10/02/2022	20	45	2022
21/10/2021	44.44	29	2021
23/05/2022	57.14	8	2022
12/11/2021	60	32	2021
04/03/2022	60	48	2022
11/11/2021	62.22	32	2021
44697.00	62.22	7	2022
18/04/2022	62.86	3	2022
11/07/2022	65	15	2022
19/01/2022	65.45	42	2022
30/12/2022	66.67	39	2022
14/02/2022	66.67	46	2022
29/04/2022	66.67	4	2022
09/02/2022	67	45	2022
04/01/2022	68	40	2022
15/05/2023	68.89	7	2023
09/05/2022	70	6	2022
18/07/2022	70	16	2022
20/06/2022	70	12	2022
20/10/2021	70	29	2021
07/03/2022	70	49	2022
27/01/2023	71.43	43	2023
03/06/2022	71.43	9	2022
03/02/2023	71.43	44	2023
07/01/2022	71.43	40	2022
28/02/2022	72	48	2022
20/07/2022	72	16	2022
09/10/2021	72.5	27	2021
28/02/2022	72.5	48	2022
28/07/2023	73.33	17	2023
26/07/2023	74	17	2023
26/10/2022	75	30	2022
07/06/2022	75	10	2022
20/05/2022	75	7	2022
31/01/2022	75	44	2022
07/03/2022	75	49	2022
12/05/2023	75.38	6	2023
10/08/2023	75.71	19	2023
26/07/2023	76	17	2023
13/04/2022	76	2	2022
01/08/2022	76	18	2022
07/07/2022	76	14	2022
06/06/2022	76	10	2022

[C4178756]

Sorry for the confusion. Hopefully I will explain it a little clear here.  I've created a measure of an average and this is dispalyed in the first graph and is correct. What I now need is a calculation of the average of each week.  So the measure will give the following averages, and now i want an overall average taken from the weeks.

WeekPF Average

1	95.07
2	94.89
3	95.58
4	92.5
5	93.81
6	94.66
7	92.77
8	92.51
9	90.56
10	96.08
11	95.49
12	94.41
13	96.32
14	96.87
15	98.06
16	91.09
17	89.33
18	93.02
19	91.66
20	93.66
21	96.2

Hope this makes sense. Let me know if not.

[Anonymous]

Hi @C4178756 ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.