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]

ExpireBox | Movies.pbix

Here is my pbix. The results for me are coming out as I expect them to, it's a little hard to say why it's not working for you without access to your data. I've used ALLSELECTED so it should still work if you filter your pivot table, so if you filter it down to one or two movies maybe it will be easier to see what is going on?

The MAX is just extracting the current filter context (ie current movie) for comparison in the filter. MAX ranks text alphabetically so MAX of one item is just the item. It should really be VALUES, but then I would need to trap the total rows and I was too lazy (total rows are meaningless, but at least don't cause the error you had earlier.

 

[Pierrecl3]

Thanks gazpage for your help!

 

[gazpage]

Thanks gazpage for your help!

Did you get the answer you wanted? What was the issue?

 

[Pierrecl3]

Did you get the answer you wanted? What was the issue?

Actually yes gazpage, thank you!
With my data, I needed to slightly modified your code by removing the column name in the ALLSELECTED of the FILTER:

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

 

[Ozeroth]

Hi Pierrecl3,

My only suggestion beyond the solution already provided is to use EXCEPT to return the difference between all select movies and movies in the current context. You could use it a few ways but for your average measure something like this:

MovieAvgExclCurrent =
AVERAGEX (
    EXCEPT (
        ALLSELECTED ( SurveyData[MovieName] ),
        VALUES ( SurveyData[MovieName] )
    ),
    [AvgLikes]
)

 

[Pierrecl3]

Thank you Ozeroth for sharing your solution.

Hi Pierrecl3,

My only suggestion beyond the solution already provided is to use EXCEPT to return the difference between all select movies and movies in the current context. You could use it a few ways but for your average measure something like this:

MovieAvgExclCurrent =
AVERAGEX (
    EXCEPT (
        ALLSELECTED ( SurveyData[MovieName] ),
        VALUES ( SurveyData[MovieName] )
    ),
    [AvgLikes]
)

 

[Pierrecl3]

The solutions mentioned so far work quite well in my previous work context.
So far I was only working on a small set of videos (like 10).

However I am now facing a new issue: when I work on 1000 videos, I have a MemoryError raising while I do have a 2016 Excel 64-bit version and 128 GB of RAM.

I take as example the computation of average of averages.
With the current implementation of that computation, the source issue is that:
- per video, Excel always recomputes the averages of all videos excluding just the one of the context filtering in order to average the averages.
Then the time complexity is exponential and apparently with Excel the space complexity is raising drastically.

My question is: is it possible to by-pass that issue with applying some memoisation (like computing the averages of all videos just once, caching all of them, then average the averages) ?

Thanks in advance for your suggestions!
Pierre