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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
csalinas
Frequent Visitor

Average from a Filtered Column with Mixed Text and Numerical Data

Hi! I have data feeding (Import mode) from a system that's reporting evaluation data. I do not have control over how that data is reported. There are four different questions in the evaluation survey, each correlated to a unique "Question_ID." The reponses all return in the same column, "Question_User_Response" of the table (raw_evaluation_report). Three of these questions provide numerical rating data (1-5) and one provides text.

 

The different "Question_ID"s are all listed in their own column. I need to filter the "Question_User_Reponse" column by a specific "Question_ID," and create an average of that numerical data. For instance, one "Question_ID" is 2003 and I need the average of the "Question_User_Reponse"s (the user's rating) for that question alone. 

 

This screenshot is of the "show as table" option on one of the graphs diplaying the evaluation totals for a specific question, which is functioning as expected. In case it matters, I created the graph by dragging "Question_User_Response" into the X and Y (which very helpfully defaults to "Count of") axes of a bar graph visuzlization and adding the filter "Question_ID is 2003."

 

Ratings Total Graph.png

 

This is what the full visualized report looks like, the table above is the one highlighted in the visualized report below:  

 

Object Evaluation Report.png

 

This is what the table looks like, showing the mixed "Question_User_Response" data in one column and a couple different "Question_ID"s in another column:

 

Evaluation Report Table.png

I have tried to create new measures or columns based off of examples others have posted in the forum and changing out the variables like the table and column names but can't seem to get it to work. I'm also coming across an occassional error message that the expressions I'm trying to use will not work with data type "string," which I think may be related to the mix of text and numerical data.

 

Would anyone know how to create an expression that filters a column by one variable of several listed in another column and then averages the result? In my case here, I need to use a specific "Question_ID" to filter the column "Question_User_Response" and return an average of the filtered data. Or, put differently and more simply, I need the average "User_Response" to a specific "Question_ID." I hope that all makes sense. Thanks in advance, you're the GOAT!

1 ACCEPTED SOLUTION
csalinas
Frequent Visitor

If it helps anyone, I was able to solve the problem. The mix of text and numerical data is an issue. To get what I needed, I selected "Transform Data" from the top ribbon, which gives you the option of duplicating the table if you right click the one to be duplicated, from the list of tables on the left of the screen. I think that's important because the duplicate option does not seem to be a part of the "Edit Query" section.

 

I renamed the table and was able to work with the data. To get my averages, I limited their information by selecting the down arrow in the column header, which lets you specify what goes into that column. For me, I limited the column to the "Question_ID"s that had numerical data and that I needed to average. I also removed a column that had text in it and I did not need. I don't know if that helped or not but since I didn't need that specific data in the duplicated table, it surely didn't hurt. I did keep another column with text that helped me keep track of the thing I was working on.

 

Then I returned to the report page with my visualization. In the list of tables on the right, my duplicated table contained the "Question_ID" and "Question_User_Response" columns. What was really important here was selecting the "Question_User_Response" column, which contained numbers that were formatted as text. and then, under "Column Tools" in the top ribbon I changed the "Data Type" to "Whole number." The text numbers became numerically formatted and, for other newbies, you can tell because a Sigma symbol appears before the column name under the duplicated table. 

 

Then I was able to use the regular filtering fields and average function in Power BI to get what I needed. To be specific, I created a table from the Visualizations section and selected my now numerical "Question_User_Reponse" column. Then I dragged "Question_ID" into the filters section, where I could select the exact "Question_ID" I needed. Then, since it was numerical data, I could now select "Average" from the drop menu under "Fields" in the Visualizations section. 

 

Boom. I got around the mixed data, as well as my totally ignorant incompetence to get the averages I needed. I cross-referenced these results with what I got from the original reporting system that was feeding the data via SQL and it's accurate. 

 

A couple takeaways for other newbies. Power BI does not seem to like mixed numerical and text data. Numerical data can be worked with using the stock drag and drop or selection tools, which are basic but effective. And numerical data that you can work with like that has a sigma symbol in front of the column name. Whew, even though this is very far from my actual job responsibilities, I'm not getting canned today! 😀

View solution in original post

1 REPLY 1
csalinas
Frequent Visitor

If it helps anyone, I was able to solve the problem. The mix of text and numerical data is an issue. To get what I needed, I selected "Transform Data" from the top ribbon, which gives you the option of duplicating the table if you right click the one to be duplicated, from the list of tables on the left of the screen. I think that's important because the duplicate option does not seem to be a part of the "Edit Query" section.

 

I renamed the table and was able to work with the data. To get my averages, I limited their information by selecting the down arrow in the column header, which lets you specify what goes into that column. For me, I limited the column to the "Question_ID"s that had numerical data and that I needed to average. I also removed a column that had text in it and I did not need. I don't know if that helped or not but since I didn't need that specific data in the duplicated table, it surely didn't hurt. I did keep another column with text that helped me keep track of the thing I was working on.

 

Then I returned to the report page with my visualization. In the list of tables on the right, my duplicated table contained the "Question_ID" and "Question_User_Response" columns. What was really important here was selecting the "Question_User_Response" column, which contained numbers that were formatted as text. and then, under "Column Tools" in the top ribbon I changed the "Data Type" to "Whole number." The text numbers became numerically formatted and, for other newbies, you can tell because a Sigma symbol appears before the column name under the duplicated table. 

 

Then I was able to use the regular filtering fields and average function in Power BI to get what I needed. To be specific, I created a table from the Visualizations section and selected my now numerical "Question_User_Reponse" column. Then I dragged "Question_ID" into the filters section, where I could select the exact "Question_ID" I needed. Then, since it was numerical data, I could now select "Average" from the drop menu under "Fields" in the Visualizations section. 

 

Boom. I got around the mixed data, as well as my totally ignorant incompetence to get the averages I needed. I cross-referenced these results with what I got from the original reporting system that was feeding the data via SQL and it's accurate. 

 

A couple takeaways for other newbies. Power BI does not seem to like mixed numerical and text data. Numerical data can be worked with using the stock drag and drop or selection tools, which are basic but effective. And numerical data that you can work with like that has a sigma symbol in front of the column name. Whew, even though this is very far from my actual job responsibilities, I'm not getting canned today! 😀

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.