Average excepts only column reference

[klehar]

Can anyone help with the above error and a workaround?

Justlike average i also need 50th percentile, 75th percentile for creating box plots

so percentile.inc is also not working with this

[FarhanJeelani · Accepted solution]

Hi @klehar ,

The error in your DAX formula occurs because the AVERAGE function only accepts a column reference as an argument, but you are passing an expression (1 - DIVIDE(netprice, listprice)).

Solution:
Instead of using AVERAGE, use the AVERAGEX function, which allows row-wise calculations on a table.

Corrected Code:
DAX

RETURN
IF(
listprice <> 0,
AVERAGEX(
TableName,
1 - DIVIDE(TableName[netprice], TableName[listprice])
),
BLANK()
)

AVERAGEX iterates over the TableName and computes the expression for each row before averaging the results.

TableName should be replaced with your actual table name containing netprice and listprice.

Please mark this post as solution if it helps you. Appreciate Kudos.

[klehar]

Thanks for the reply

What about percentile.inc?

[FarhanJeelani]

Hi @klehar ,

Just like AVERAGE, the PERCENTILE.INC function in Power BI also requires a column reference and does not accept row-wise calculations directly.

Use PERCENTILEX.INC Instead
To calculate percentiles based on an expression, use PERCENTILEX.INC, which allows iteration over a table (similar to AVERAGEX).

Corrected Code for 50th & 75th Percentile
DAX

RETURN
IF(
SELECTEDVALUE(TableName[listprice]) <> 0,
PERCENTILEX.INC(
TableName,
1 - DIVIDE(TableName[netprice], TableName[listprice]),
0.50 -- Change to 0.75 for the 75th percentile
),
BLANK()
)

Please mark this post as solution if it helps you. Appreciate Kudos.