Bypass ‘multiple row context’ in DAX computation for Excel Pivot Table

[Pierrecl3]

<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!

 

[gazpage]

Would expect measure 2 to be something like:

CALCULATE ( 
    [AvgLikes],
    FILTER ( 
        ALLSELECTED ( SurveyData[MovieName] ),
        SurveyData[MovieName] <> EARLIER ( SurveyData[MovieName )
    )
)

Or maybe replace EARLIER with VALUES.

 

[Ozeroth]

It can definitely be done.

Which version of Excel do you have?

If you have 2016, then measures 2 & 3 can be written using the EXCEPT function.
In earlier versions, it can be done with slightly more long-winded code since EXCEPT is not available.

 

[Pierrecl3]

Would expect measure 2 to be something like:

CALCULATE ( 
    [AvgLikes],
    FILTER ( 
        ALLSELECTED ( SurveyData[MovieName] ),
        SurveyData[MovieName] <> EARLIER ( SurveyData[MovieName )
    )
)

Or maybe replace EARLIER with VALUES.

Thank you gazpage for your answer.
I did try your code with EARLIER, it does not work because of no earlier row context.
When I replace EARLIER with VALUES, the formula has no errors but Excel raises a calculation error in the measure: 'A table of multiple values was supplied where a single value was expected.'

Furthermore, I need to compute the average and standard deviation of [AvgLikes]. However in your code, those computations do not seem to be mentioned. Am I wrong?

 

[Pierrecl3]

It can definitely be done.

Which version of Excel do you have?

If you have 2016, then measures 2 & 3 can be written using the EXCEPT function.
In earlier versions, it can be done with slightly more long-winded code since EXCEPT is not available.

Thanks for your insight Ozeroth!
I have the 2016 Excel version on Windows! What would be your solution?

 

[gazpage]

Thank you gazpage for your answer.
I did try your code with EARLIER, it does not work because of no earlier row context.
When I replace EARLIER with VALUES, the formula has no errors but Excel raises a calculation error in the measure: 'A table of multiple values was supplied where a single value was expected.'

Furthermore, I need to compute the average and standard deviation of [AvgLikes]. However in your code, those computations do not seem to be mentioned. Am I wrong?

I tried to reply to this earlier but it looks like it got lost. Hopefully I don't end up with a double post. The corrected version of what I thought you wanted would be this.

CALCULATE ( 
    [AvgLikes],
    FILTER ( 
        ALLSELECTED ( SurveyData[MovieName] ),
        SurveyData[MovieName] <> MAX ( SurveyData[MovieName] )
    )
)

This takes the average number of likes, excluding any rows that have the current MovieName. Do I now understand that you want the average of each Movie's average? That is if one movie has one like and no dislikes, so an average of 1, and another has no likes and one billion dislikes, so an average of 0, then the average of those should be 0.5, rather than something very close to 0?

 

[gazpage]

Here is the other version of the code:

MovieAvgExclCurrent = 
CALCULATE ( 
    AVERAGEX(
        ALLSELECTED ( SurveyData[MovieName] ),
        [AvgLikes]
    ),
    FILTER ( 
        ALLSELECTED ( SurveyData[MovieName] ),
        SurveyData[MovieName] <> MAX ( SurveyData[MovieName] )
    )
)

NB that the totals and subtotals won't work here. You would need to use HASONEVALUE or ISFILTERED to calculate those.

 

[gazpage]

Oh, to do the standard deviation, create a measure [STDVLikes] that is the same as [AvgLikes] but uses STDEV.S or STDEV.P. If you need the equivalent of MovieSTDVExclCurrent then replace AVERAGEX with STDEVX.

 

[Pierrecl3]

I tried to reply to this earlier but it looks like it got lost. Hopefully I don't end up with a double post. The corrected version of what I thought you wanted would be this.

CALCULATE ( 
    [AvgLikes],
    FILTER ( 
        ALLSELECTED ( SurveyData[MovieName] ),
        SurveyData[MovieName] <> MAX ( SurveyData[MovieName] )
    )
)

This takes the average number of likes, excluding any rows that have the current MovieName. Do I now understand that you want the average of each Movie's average? That is if one movie has one like and no dislikes, so an average of 1, and another has no likes and one billion dislikes, so an average of 0, then the average of those should be 0.5, rather than something very close to 0?

Yes you understand well gazpage.
I need the average of movies' averages (with the current movie's average excluded) - while not averaging on all the data except the current movie.
Following your example, if movie A has an average of 1, movie B an average of 0 and movie C an average of 0.6, the result should be for movie C row, the average of movies A & B averages, i.e. 0.5.
That's why I asked why your code did not mention any average function.

 

[Pierrecl3]

Here is the other version of the code:

MovieAvgExclCurrent = 
CALCULATE ( 
    AVERAGEX(
        ALLSELECTED ( SurveyData[MovieName] ),
        [AvgLikes]
    ),
    FILTER ( 
        ALLSELECTED ( SurveyData[MovieName] ),
        SurveyData[MovieName] <> MAX ( SurveyData[MovieName] )
    )
)

NB that the totals and subtotals won't work here. You would need to use HASONEVALUE or ISFILTERED to calculate those.

Thanks gazpage for your code!
I have just tested it; it "almost" works.. because the results are pretty near but not similar. For instance, the code returns 0.9392 instead of 0.9251.
I have also a question: could you explain me why do you extract MAX ( SurveyData[MovieName] ) to use it in the filter? Is this maximum the current movie to exclude?