Average with fake votes

[SebaSpotti]

Hi guys!

I have a simple table with a column of votes called Table1[Evaluation]. In this column i can have votes from 0 to 10. I want to calculate the average of this column but in a particular way: remove all 0 and 10; and a number of 0 and 10 equal to the average of the count of the others votes. 

An example to better understand:

Table1[Evaluation] = [0,0,1,3,4,5,1,9,8,4,5,10,10,0,10]

Average of Count 1_9 = 1

Calculate the average of this column [1,3,4,5,1,9,8,4,5,0,10]

Any suggestions?

Thanks all!

Seba

[v-linhuizh-msft]

Thanks for the reply from Sahir_Maharaj.

Hi @SebaSpotti ,

I'll offer another approach.

The simple data is as follows:

Please create a measure:

Average = 
VAR _count_1_9=COUNTX(FILTER(ALL('Table1'),NOT'Table1'[Evaluation] IN {0,10}),'Table1'[Evaluation])
VAR _countallwithout0and10=COUNTX(FILTER(ALL('Table1'),NOT'Table1'[Evaluation] IN {0,10}),'Table1'[Evaluation])
VAR _AverageofCount1_9=DIVIDE(_count_1_9,_countallwithout0and10)
VAR _count=COUNTROWS(FILTER(ALL('Table1'),NOT('Table1'[Evaluation] IN {0,10})))
VAR _sum=SUMX(FILTER(ALL('Table1'),NOT('Table1'[Evaluation] IN {0,10})),'Table1'[Evaluation])
RETURN
(_sum+(0+10)*_AverageofCount1_9)/(_count+2)

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly

[SebaSpotti]

It works for my need, but I prefer the other solution where the table is "cut". Thanks anyway!

[Sahir_Maharaj · Accepted solution]

Hello @SebaSpotti,

Can you please try this approach:

Modified Average = 
VAR Count1_9 = 
    COUNTROWS(
        FILTER(
            Table1,
            Table1[Evaluation] >= 1 && Table1[Evaluation] <= 9
        )
    )

VAR UniqueCount1_9 = 
    COUNTROWS(
        DISTINCT(
            FILTER(
                Table1,
                Table1[Evaluation] >= 1 && Table1[Evaluation] <= 9
            )
        )
    )

VAR AvgCount1_9 = 
    DIVIDE(Count1_9, UniqueCount1_9, 0)

VAR LimitedZeros = 
    MIN(
        COUNTROWS(FILTER(Table1, Table1[Evaluation] = 0)),
        AvgCount1_9
    )

VAR LimitedTens = 
    MIN(
        COUNTROWS(FILTER(Table1, Table1[Evaluation] = 10)),
        AvgCount1_9
    )

VAR UpdatedTable = 
    UNION(
        FILTER(Table1, Table1[Evaluation] >= 1 && Table1[Evaluation] <= 9),
        TOPN(LimitedZeros, FILTER(Table1, Table1[Evaluation] = 0)),
        TOPN(LimitedTens, FILTER(Table1, Table1[Evaluation] = 10))
    )

RETURN
    AVERAGEX(UpdatedTable, Table1[Evaluation])