<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'American Typewriter'}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'American Typewriter'; min-height: 14.0px}span.s1 {text-decoration: underline}span.Apple-tab-span {white-spacere}</style>My original relational table named SurveyData - extracted by an SQL query in an OLAP fashion - is formed by 3 columns:
- MovieName : string
- ParticipantID : integer
- LikeLabel : integer 0 or 1
I want to extract a Pivot Table.
First I drag the MovieName in the Rows field of the PivotTable.
Then I need to compute three measures written in a DAX fashion in the PivotTable Fields of SurveyData table:
1st measure: given the movie related to the row label, I compute the average of participants liking the movie;
this measure is: AvgLikes := AVERAGE(SurveyData[LikeLabel])
2nd measure: given the movie related to the row label, I exclude this movie from the set of N movies and I need to compute the average (e.g. with AVERAGEX) of AvgLikes of the remaining N-1 movies
3rd measure: given the movie related to the row label, I exclude this movie from the set of N movies and I need to compute the standard deviation of AvgLikes of the remaining N-1 movies
Currently I do not know how to compute the 2nd & 3rd measures.
Needless to say, the set of N movies also needs to be dependent of the selection specified in Row Labels of the Pivot Table in the Excel spreadsheet.
Is it possible to create those measures? I hope having been clear enough in my explanations of the matter.
Thanks in advance for your insights!
- MovieName : string
- ParticipantID : integer
- LikeLabel : integer 0 or 1
I want to extract a Pivot Table.
First I drag the MovieName in the Rows field of the PivotTable.
Then I need to compute three measures written in a DAX fashion in the PivotTable Fields of SurveyData table:
1st measure: given the movie related to the row label, I compute the average of participants liking the movie;
this measure is: AvgLikes := AVERAGE(SurveyData[LikeLabel])
2nd measure: given the movie related to the row label, I exclude this movie from the set of N movies and I need to compute the average (e.g. with AVERAGEX) of AvgLikes of the remaining N-1 movies
3rd measure: given the movie related to the row label, I exclude this movie from the set of N movies and I need to compute the standard deviation of AvgLikes of the remaining N-1 movies
Currently I do not know how to compute the 2nd & 3rd measures.
Needless to say, the set of N movies also needs to be dependent of the selection specified in Row Labels of the Pivot Table in the Excel spreadsheet.
Is it possible to create those measures? I hope having been clear enough in my explanations of the matter.
Thanks in advance for your insights!