CODEWITHSUNDEEP
powerbidaxpowerquery
Ignpl
Ignpl

Reputation: 39

Calculate the average excluding some values

I have a table (TABLE1) similar to this:

DATE VALUE
01/01/2022 4
01/01/2022 3
01/01/2022 5
01/01/2022 8
02/01/2022 9
02/01/2022 8
02/01/2022 7
02/01/2022 3

I would like to calculate for each day the average value excluding the values that are less than the general average.

For example, for the 01/01/2022 the average is (4+3+5+8)/4 = 5 and the value that I want to calculate is the average excluding the values undder than this average (5+8)/2 = 6,5

Hope you can help me with a measure to calculate this. Thanks!!

Upvotes: 1

Views: 1038

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

Test this measure:

AverageValue = 
VAR AllAverage = CALCULATE(AVERAGE(TestTable[VALUE]),ALLEXCEPT(TestTable,TestTable[DATE]))
VAR TblSummary = ADDCOLUMNS(
    VALUES(TestTable[DATE]),
    "AvgValue",CALCULATE(AVERAGE(TestTable[VALUE]),TestTable[VALUE]>=AllAverage)
)
RETURN
AVERAGEX(TblSummary,[AvgValue])

DF

Upvotes: 1

Related Questions