Forum Discussion

klehar's avatar
klehar
Helper V
1 year ago
Solved

Average excepts only column reference

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
Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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.

FarhanJeelani's avatar
FarhanJeelani
Super User
1 year ago

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's avatar
klehar
Helper V
1 year ago

Thanks for the reply

What about percentile.inc?

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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.