How to create average

[kimdiep503]

Hi everyone, I'm trying to find the average NPS score based on the data below, excluding any blanks. Can someone help with creating a formula or solution on how I can get the result in PowerBI? Thanks in advance.

Ticket Number	Net Promotor Score
1	Blank
2	10
3	10
4	8
5	Blank
6	9
7	9
8	10
9	Blank
10	Blank
Average	9.3333

[amustafa · Accepted solution]

Assuming in your input table you have literal values 'Blank' then here's how you can exclude them and create a new measure Avg. NPS. (adjust your table and Column name.

Avg. NPS = AVERAGEX(

    FILTER(Table1,

        ISNUMBER(VALUE(Table1[Net Promotor Score])) && Table1[Net Promotor Score] <> "Blank"

    ),

    VALUE(Table1[Net Promotor Score])

)

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[Anonymous · Accepted solution]

Hi @kimdiep503 

You can use the average() function directly.

e.g You can create a calculated column.

Column = AVERAGE('Table'[Net Promotor Score])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amustafa · Accepted solution]

Assuming in your input table you have literal values 'Blank' then here's how you can exclude them and create a new measure Avg. NPS. (adjust your table and Column name.

Avg. NPS = AVERAGEX(

    FILTER(Table1,

        ISNUMBER(VALUE(Table1[Net Promotor Score])) && Table1[Net Promotor Score] <> "Blank"

    ),

    VALUE(Table1[Net Promotor Score])

)

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/