Snatch - A Damn Fine Movie

 

“However…  you do have ALL() the characteristics of a dog, Gary.  ALLEXCEPT() loyalty…”

 

 

 

This will be a quick one.  I think.

In part two, we left off with the observation that VALUES() can “trump” ALL() even when VALUES() is applied to a column that is not on the pivot:

[ALL then VALUES of field not on pivot] =
[Total Sales](ALL(Periods),VALUES(Periods[Period Num]))

image_thumb5

One thing I have often puzzled over is this:  how does a combination of ALL(Table), VALUES(Table1[ColumnA]) compare to using ALLEXCEPT(Table1, Table1[ColumnA])?

In essence, those should do the same things right?  In one case, you set the whole table to ALL(), but then “restore” ColumnA to its pivot context using VALUES().  In the other case, you set every field in the table except ColumnA to ALL(), leaving ColumnA in the context set by the pivot.

They should result in the same thing, right?  Let’s check.

ALLEXCEPT() using columns that are on the pivot

[ALLEXCEPT fields on Pivot]=
[Total Sales](ALLEXCEPT(Periods,Periods[Year],
Periods[FiscalQuarter]))

There are two fields on the pivot from the Periods table – Year and FiscalQuarter, and both of those are excluded from being “all’d” in the measure.  Results:

image

OK, good news.  Those are indeed the same results as the unfiltered base measure, and that’s what we got from ALL() plus VALUES() as well.  So from this, we can say that ALL() plus VALUES() is the same as ALLEXCEPT().

Not so fast though…

ALLEXCEPT() using columns that are NOT on the pivot

Let’s try ALLEXCEPT() using a field that is NOT on the pivot.  Remember, VALUES() trumped ALL() in this case, too, and the results were the same as the unfiltered base measure.  Will ALLEXCEPT follow suit?

[ALLEXCEPT field NOT on Pivot]=
[Total Sales](ALLEXCEPT(Periods,Periods[Period Num]))

image

OK, that was a bit unexpected.  ALLEXCEPT() returned the same results as pure ALL().  So ALLEXCEPT() does *not* preserve the pivot context of columns NOT on the pivot, whereas VALUES() *does* pick those up.

I’m not sure if that’s intentional on the part of the PowerPivot team, or if it’s just a quirk.

Summary

1) ALLEXCEPT() does behave precisely like ALL(), VALUES() as long as the “exempted” columns are columns on the pivot.

2) ALLEXCEPT() does NOT preserve pivot context, however, on columns that are not on the pivot.

Oh, and one more…

No need to use ALLEXCEPT() instead of ALL() when using VALUES()

In the past, whenever I wanted to do an ALL() with a VALUES(), I typically used an ALLEXCEPT() instead of the ALL().

For instance, I would do something like this:

[Total Sales](ALLEXCEPT(Periods,Periods[ColumnA]),
VALUES(Periods[ColumnA]))

That turns out to have been an overly-careful habit.  As we’ve seen here, and in part two, VALUES() trumps the ALL(), and does not need the “help” of ALLEXCEPT() instead.

Yes, I’m sure many of you will say that’s obvious from everything covered so far, but just in case you find yourself tempted to use ALLEXCEPT() to exempt the column you are preserving with VALUES(), there’s no need.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 2 Comments

  1. I have created a measure in Power Pivot and I am able to calculate subtotal from it and repeat the values in another column as shown below. The issue is that it doesnt take into account the external filters on the pivot table.

    Client | Type | SubType | DT(1/2) | DT(1/3) | DT(1/4)
    —————————————————————————————————————-
    | | |CurrDay | PrevDayST | CurrDay | PrevDayST | CurrDay | PrevDayST
    —————————————————————————————————————–
    ABC X AA 100 0 101 600 1000 306
    ABC X BB 200 0 102 600 2000 306
    ABC X CC 300 0 103 600 3000 306
    ZZZ Y AA 450 0 100 1500 4000 505
    ZZZ Y BB 550 0 205 1500 2000 505
    ZZZ Y CC 500 0 200 1500 3000 505

    Here are the steps –

    1) I created a measure to calculate Previous Day Amount –
    *Table1 contains all the data

    *Table2 is the Date table

    *Table1 and Table2 are linked

    PREV-DT-AMT= CALCULATE(SUM(Table1[NET-AMOUNT]), FILTER(all(Table2[DT]), Table2[DT] < max(Table2[DT]) ))
    2) In this step I calculate subtotals shown in above table under column "PrevDayST"

    =calculate([PREV-DT-AMT],
    allexcept(Table1,Table1[CLIENT],Table1[Type],Table2[DT]))

    The issue here is that when I use filters on the pivot table to filter out some Sub-Types the subtotal measure in step2 doesnt take into account the filters in the pivot table. I understand that allexcept ignores all the external filters. I would highly appreciate if you could give me a solution that will let me calculate subtotal from measure [PREV-DT-AMT] and repeat it under "PrevDayST" WITHOUT ignoring external filters.

    I have been struggling with this for the past couple of days. Thanks in advance.

Leave a Comment or Question