Average Aggregated Data

[BumKneesOhYeah]

I have a table like following.  **Note I put an empty column in tables so can read Value....without empty column the numbers were hard to read.

Company	Month		Value
1	1/1/2025		5
1	2/1/2025		40
1	2/1/2025		50
5	2/1/2025		10
5	2/1/2025		20
5	2/1/2025		30
5	3/1/2025		0
5	3/1/2025		10

For scoring I want to average the Value by Company and month

Company	Month		Value
1	1/1/2025		5
1	2/1/2025		45
5	2/1/2025		20
5	3/1/2025		5

Then I want to average by Company the above table.  So when shown in visual with Company only, it should be like below

Company		Value
1		25
5		12.5

Then At Total level it should be average of above: 18.75.

I have tried using Summarize/addcolumns/AverageX and no luck.  What I have now is following which works except at Total level:

Meeasure =

VAR MonthlyAverages =

    SUMMARIZE(

        data,

        data[Company],

        data[Month],

        "Monthly/Company Average", CALCULATE(AVERAGEX( data, data[Value] ))

    )

var ContractorScoringTable =

SUMMARIZE(

    MonthlyAverages,

   data[Company],

    "Score per Company",AVERAGEX(MonthlyAverages,[Monthly/Company Average])

)

VAR Result =

AVERAGEX(ContractorScoringTable,

    [Score per Company])

RETURN Result

[Cookistador · Accepted solution]

You can use the following measure in a matrix:

Average Measure= 
AVERAGEX(
    VALUES('Table'[Company]), 
    AVERAGEX(
        VALUES('Table'[Month]), 
        CALCULATE(AVERAGE('Table'[Value]))
    )
)

This is the result I get:

Do not hesitate to ask if you need more support

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[BumKneesOhYeah]

Thank you for input.  Cookistador's solution was the exact one I was looking for. I think yours might've been averaging in a different order because the Totals came out as 13.33, but I was looking for 18.75.

[Ashish_Mathur · Accepted solution]

You are welcome.  This simple additional measures will get your desired result

Measure 2 = AVERAGEX(VALUES(Data[Company]),[Measure])

Hope this helps.

[Cookistador · Accepted solution]

You can use the following measure in a matrix:

Average Measure= 
AVERAGEX(
    VALUES('Table'[Company]), 
    AVERAGEX(
        VALUES('Table'[Month]), 
        CALCULATE(AVERAGE('Table'[Value]))
    )
)

This is the result I get:

Do not hesitate to ask if you need more support

[BumKneesOhYeah]

Thank you, this worked perfect!  Now I am going to try on more complicated Measure rather than value!