Average If Greater Than 0

[SofaQueen]

Hello,

I am looking to add a column which has average value of my data across the row, excluding values below 0.

Row 1 value would be DATA1+DATA2+DATA3+DATA4 / SUBSIZE

Row 2 value would be DATA1+DATA2+DATA3+DATA4+DATA5+DATA6 / SUBSIZE

Thanks in advance for any advice!

[parry2k · Accepted solution]

@SofaQueen if my previous reply is the use case to calculate avg then add a column using the following expression.

Avg = 
DIVIDE( 
Table[Data1] + Table[Data2] + Table[Data3] + Table[Data4] +
IF ( Table[SubSize] = 6,  Table[Data5] + Table[Data6], 0 ),
Table[Subsize]
)

Would appreciate Kudos 🙂 if my solution helped. Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

[bheepatel · Accepted solution]

@SofaQueen 

You can try using the following function:

Average = 

VAR vDataPoint1 = IF(DATA1 > 0, DATA1, 0)

VAR vDataPoint2 = IF(DATA2 > 0, DATA2, 0)

...

VAR vDataPoint6 = IF(DATA6 > 0, DATA6, 0)

RETURN (vDataPoint1 + vDataPoint2 + ... + vDataPoint6 ) / SUBSIZE

[parry2k]

@SofaQueen what is the logic to add data 1 to 4 in first row and data 1 to 6 column in 2nd row, is it defined based on value in subsize column?

[SofaQueen]

 Hi @parry2k .  There is no logic. This is raw measurement data from a software database.  The SUBSIZE column is populated based on the quantity of values that is entered into that system.  If no value is input, the <null> has translated into that -1.6E+98 amount when synced w/ PBI.

[parry2k · Accepted solution]

@SofaQueen if my previous reply is the use case to calculate avg then add a column using the following expression.

Avg = 
DIVIDE( 
Table[Data1] + Table[Data2] + Table[Data3] + Table[Data4] +
IF ( Table[SubSize] = 6,  Table[Data5] + Table[Data6], 0 ),
Table[Subsize]
)

Would appreciate Kudos 🙂 if my solution helped. Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

[SofaQueen]

Perfecto!  Thank you so very much!

[Vvelarde]

@SofaQueen 

Hi, One way to obtain the SUM without 0 is this:

Av =
VAR _tempTable =
    FILTER (
        UNION (
            ROW ( "Rating"; 'Table'[RatingA] );
            ROW ( "Rating"; 'Table'[Rating B] );
            ROW ( "Rating"; 'Table'[Rating C] )
        );
        [Rating] >= 0
    )
RETURN
    SUMX ( _tempTable; [Rating] )

Another will be with IF

AV=IF('Table'[Data1] >=0;Table'[Data1];0) +IF('Table'[Data2] >=0;Table'[Data2];0) ..............

Regards

Victor