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

Pierrecl3

New Member
Joined
Jul 26, 2017
Messages
18
<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-space:pre}</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!
 
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.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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])))
 
Last edited:
Upvote 0
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:

Code:
MovieAvgExclCurrent =
AVERAGEX (
    EXCEPT (
        ALLSELECTED ( SurveyData[MovieName] ),
        VALUES ( SurveyData[MovieName] )
    ),
    [AvgLikes]
)
 
Last edited:
Upvote 0
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:

Code:
MovieAvgExclCurrent =
AVERAGEX (
    EXCEPT (
        ALLSELECTED ( SurveyData[MovieName] ),
        VALUES ( SurveyData[MovieName] )
    ),
    [AvgLikes]
)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top