cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-nankh
Frequent Visitor

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.

 

CategoryIDSubcategoryServiceRatingWeightWeighted_Average
AssetManagement(AM)AM1Azure21002
AssetManagement(AM)AM2Azure31003
AssetManagement(AM)AM3Azure21002
AssetManagement(AM)AM4AzureNA100 

 

Below is the view of the result table that I want in PowerBICapture.PNGThe 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

 

1 ACCEPTED SOLUTION

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)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

Hi @v-nankh

 

Is this the result you are expcting?

 

na.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





v-nankh
Frequent Visitor

@parry2kYes 🙂

Ok, so add a measure for average calculation , if you already have just change it as below:

 

Avg = 
var a = AVERAGE(Table3[Rating])
return if(ISBLANK(a) || a = 0, "NA", a)





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





v-nankh
Frequent Visitor

@parry2kWeighted_Avg is being calculated in SQl and thats a float field. its not accepting NA since its a varchar

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)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

v-nankh
Frequent Visitor

@parry2kThank you so much Smiley Happy 

It's working!

 

I have another question. I am trying to apply conditional formatting. If the average is below 3 then it should be colored as red , if the average is above is 3 it should be green and if its NA then it should be grey. I was able to apply conditional formatting for below and above number 3 but not for NA. 

Hi @v-nankh,

If you have resolved your issue, please mark the right reply as answer. More people will find the solution easily.

Best Regards,
Angelia

Glad to hear it is working, not sure if color formatting can be done as you described.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Sorry replied too quickly, i think what you can do is change values background color to grey and then conditional formatting will take care of color where applicable and NA will be grey. (I guessed it), try it.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!