Forum Discussion
Matrix - How to add a string value in a number column
I am creating a PowerBI Report with a number column which basically has Weighted Average and that I have calculated in SQL. The weighted average is calculated based on the rating and weight for each NIST category and subcategory against Microsoft's various Services. The rating could be "1,2,3,4,NA". Logically if the rating is NA and I use weighted_Avg formula, SQL gives a Null for that particular cell.
Currently this report is being generated manually in excel. So in the final table after the calculation of weighted_avg wherever the rating was NA, the weighted average is also written as NA in excel. But since I am trying to automate it and doing the calculation in SQL and pulling the data in PowerBI, in that column instead of NA the cell is blank.
Now the stakeholder want NA instead of a blank in PowerBI matrix with the weighted_avg against each category and sub category in rows and Microsoft Services in column.
My question: Is it possible to put a string "NA" in a number field in a matrix at a subcategory level. I am aggregating it as "Average" at category level.
Below is the raw table data. I am getting the value for weighted_avg column by calculating it in SQL.
| CategoryID | Subcategory | Service | Rating | Weight | Weighted_Average |
| AssetManagement(AM) | AM1 | Azure | 2 | 100 | 2 |
| AssetManagement(AM) | AM2 | Azure | 3 | 100 | 3 |
| AssetManagement(AM) | AM3 | Azure | 2 | 100 | 2 |
| AssetManagement(AM) | AM4 | Azure | NA | 100 |
Below is the view of the result table that I want in PowerBIThe yellow highlighted cell gives the average of subcategories weighted_average (2,3,2). Against AM4 currently the cell is blank but I want "NA" since in the raw data the Rating of this subcategory is "NA".
I would really appreciate if someone could help me with my query.
Please let me know if you need more information.
Thanks,
Nancy Khanduja
You still have add new measure and change the formula like this, I was not aware if average is pre-calculated.
Add following measure and use it in your report.
My Average = var myAvg = Average(Table3[Weighted_Average]) return if(ISBLANK(myAvg), "NA", myAvg)
