AllExcept

[paivers]

I am trying to calculate the sum of members across all months selected in the spreadsheet for use in another measure. If I just filter like this I get 30, since it is applying the row context: JustFilter:=sumx(filter(factMemberMonth,[Period]="Prior"),[Members]) . So I wanted to Expand the context to all but get 720 (24 months x 30 in database), which is too many as I filter in the spreadsheet, like this: All:=sumx(filter(All(factMemberMonth),[Period]="Prior"),[Members]). Then I tried AllExcept which surprised me by returning 570 that is (19 * 30) - neither 24 months or the selected 12, like this AllExcept:=sumx(filter(AllExcept(factMemberMonth,factMemberMonth[Month]),[Period]="Prior"),[Members]). The data looks like this in Excel, although there are 24 months in the data, that are all Prior period. The sum I am looking for is 360. Seems like it should be easier but I can do all or row, but not selected rows only.[TABLE="width: 413"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]Members
[/TD]
[TD]JustFilter
[/TD]
[TD]All
[/TD]
[TD]AllExcept
[/TD]
[TD]Sum
[/TD]
[/TR]
[TR]
[TD]-12
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-11
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-10
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-9
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-8
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-7
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-6
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-5
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-4
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-3
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-2
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]-1
[/TD]
[TD]30
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD][/TD]
[TD="align: right"]360
[/TD]
[TD="align: right"]720
[/TD]
[TD="align: right"]570
[/TD]
[TD="align: right"]360
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Laurent C]

If you want to calculate a subtotal for selected rows only, you should use the ALLSELECTED function.

The ALLEXCEPT function takes your fact table without any filter, removes the month column, and returns all distinct combinations from the remaining columns.

This is not the same as just removing filters except on the month column.

 

[paivers]

Perfect, thanks Laurent.