Forum Discussion

BumKneesOhYeah's avatar
BumKneesOhYeah
Regular Visitor
8 months ago
Solved

Average Aggregated Data

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.

CompanyMonth Value
11/1/2025 5
12/1/2025 40
12/1/2025 50
52/1/2025 10
52/1/2025 20
52/1/2025 30
53/1/2025 0
53/1/2025 10

 

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

CompanyMonth Value
11/1/2025 5
12/1/2025 45
52/1/2025 20
53/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

 

Need Help
  • Cookistador's avatar
    Cookistador
    8 months ago

    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's avatar
    Ashish_Mathur
    8 months ago

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

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

    Hope this helps.

     

5 Replies

  • Cookistador's avatar
    Cookistador
    Super User
    8 months ago

    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's avatar
      BumKneesOhYeah
      Regular Visitor
      8 months ago

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

    • BumKneesOhYeah's avatar
      BumKneesOhYeah
      Regular Visitor
      8 months ago

      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's avatar
        Ashish_Mathur
        Super User
        8 months ago

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

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

        Hope this helps.