Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@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)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@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)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.