CODEWITHSUNDEEP
powerbidax
Devin Smith
Devin Smith

Reputation: 67

DAX AVERAGE() function differs from table average of column

I am new to DAX and running into a problem with regard to averages.

In PowerBI I am using a table with some dimensions and measures, the measures obviously require some form of summary so the dimension can roll up/summarize the data.

The problem is, I have a measure let's call Minutes that I want to average across the 5 lines of data, I drag the column minutes on set the summary in the table to average and it works perfectly by splitting the data according to the dimensions - data example:

 1. A 10
 2. A 8
 3. A 10
 4. A 7
 5. A 5
 6. B 10
 7. B 10
 8. B 9
 9. B 9
 10. B 10

Output in Table:

 1. A 8
 2. B 9.6

If I want to use that Average of 8 and 9.6 in another calculation and I create a new column called AvgMins = AVERAGE(Minutes) and drag it onto the grid I get a value of 8.8 for both A and B - I understand that the most likely reason for this is due to the calculation happening before the dimension splits and therefore the grid can't handle it - but how do I handle this in the DAX column calc itself?

As pointed out by Jos I was creating the calculation as a column instead of a measure, changing to a measure the normal AVERAGE() works perfectly

Upvotes: 0

Views: 1075

Answers (3)

Mik
Mik

Reputation: 2103

If you are looking for Average of Average.

AVERAGEX(
    VALUES('Table'[CategoryColumnName])
    ,CALCULATE(AVEARGE('Table'[Minutes]))
    )

This if you are looking for Average per category

CALCULATE(
    AVERAGE(
        AVEARGE('Table'[Minutes]))
        ,ALLEXCEPT('Table','Table'[CategoryColumnName])
    )
)

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

You should be using a Measure, not a Calculated Column.

Upvotes: 2

Peter
Peter

Reputation: 12335

A measure can result in a lot of different numbers - depending on the filter context.

AbgMins = AVERAGE('Table'[Minutes])

w/o any filter will return the average of the Minutes column, which is 8.8. But if you filter it by your category - A and B - it will return the average for all A's and all B's, which is 8 and 9.6.

Upvotes: 1

Related Questions