Need help with Average with two criteria

[PoonamS]

Hi - I need to calculate average value of Value column for sprecific questions in Label column and Dimension column. Please see table below. (Value column has both text and number strings for questions.)

I tried pivoting Label column with Value and add AVG measure; however, I get this error message. Is there a way to workaround this?

Dimension	Label	Value
PA	Over the course of this program, how frequently were you able to apply what you learned in managing data as an asset in your work?	5
PA	How will you use your learnings to establish personal accountability for managing data as an asset within your daily work and behaviors?	Lorem ipsum dolor sit amet, consectetur adipiscing elit.
Trust	Over the course of this program, how frequently were you able to apply what you learned in building trust and confidence in data  in your work?	3
Trust	How will you use your learnings to build more trust and confidence in data within your daily work and behaviors (instructions within question: Consider our foundational digital platforms in your answer)	Lorem ipsum dolor sit amet, consectetur adipiscing elit.
Value	Over the course of this program, how frequently were you able to apply what you learned in using data to gain insights that deliver business value in your work?	3
Value	How will you use your learnings to use data to gain insights that deliver business value within your daily work and behaviors ?	Lorem ipsum dolor sit amet, consectetur adipiscing elit.

[PoonamS]

Hi @amitchandak 

That's what I thought that data type is text but I had worked this data in PowerQuery.  When I select detec data for COlumn PA for which I am using Average Measure - it shows it as whole number. You can see on RHS how to pivot.

[ryan_mayu]

@PoonamS 

Are PA column and PA adoption column the different column? You are using average on the PA adoption column

[PoonamS]

yes, PA adoption was name of the measure I created for avg of PA column that was drawing blank due to data type being text instead of whole number. 

[amitchandak · Accepted solution]

@PoonamS , Also validate in Column tools, after clicking on the column in the filed pane in reporting view.

Can you share a sample pbix after removing sensitive data?

[PoonamS]

yes, it worked. For some reason, it was not updated data text type - it is fixed. THank you so much Amit. 

[amitchandak]

@PoonamS , You are trying to use Average on column with text data type,

Either measure or default summarization or aggregation at visual level has that