Requirement

We have a table like (1). This tables shows how many units of product has been sold on each date in each region. We want to get pivot table (2) that shows how many days, sale was bigger than 75, 53 or 27 units. We want to observe those values per year so we added a slicer for years (3).

Needed calculations

If our data is (1), first we need to group our data by Region and OrderDate and to calculate sum of units for each group (2). That table we have to filter for values bigger than 75 (3). Then, again, we have to do grouping by Region and to count rows from our filtered table (4). We also have to repeat this for values 53 and 27.

Slow solution

For this calculation we could create three measures that look like the one bellow. If we have a lot of data, after clicking on slicer we would have to wait a lot for calculation to finish. Not only we do have a lot of data, but we also have demanding calculation for each measure, and we have three such measures.

MeasureOver75:=
VAR GroupByRegionOrderDate = GROUPBY( SampleTable; [Region]; [OrderDate]; "SumOfUnits"; SUMX( CURRENTGROUP(); [Units] ) )
VAR FilterOver75AndCount = COUNTROWS( FILTER( GroupByRegionOrderDate; [SumOfUnits] >= 75 ) )
RETURN FilterOver75AndCount

All three measures starts with the same step ( "GroupByRegionOrderDate" ). This step is very expensive because it is the one doing most of calculations. This step returns table. If we could just do this step once instead of repeating it for each measure, that would be great. We could make a basic measure that returns such table (1), and then three other measures that would use that table to calculate final scalar values (2).

Unfortunately, measures can not return table. They can only return scalar.

Better solution

Better solution is to create one measure that returns all three values. We can simply concatenate three numbers in one string ( "25-32-36" ). That would be result of our Basic measure. Then we can create three measures that would extract separate values. This could be our Basic measure code:

BasicMeasureString:=
VAR GroupByRegionOrderDate = GROUPBY( SampleTable; [Region]; [OrderDate]; "SumOfUnits"; SUMX( CURRENTGROUP(); [Units] ) )
VAR BiggerThen75 = COUNTROWS( FILTER( GroupByRegionOrderDate; [SumOfUnits] >= 75 ) )
VAR BiggerThen53 = COUNTROWS( FILTER( GroupByRegionOrderDate; [SumOfUnits] >= 53 ) )
VAR BiggerThen27 = COUNTROWS( FILTER( GroupByRegionOrderDate; [SumOfUnits] >= 27 ) )
VAR Result = BiggerThen75 & "-" & BiggerThen53 & "-" & BiggerThen27
RETURN Result           

It would return string „25-32-36". Then we can use three additional measures to extract three separate values:

FirstPartOfString:=VALUE( LEFT( [BasicMeasureString]; 2 ) )             //returns 25
MiddlePartOfString:=VALUE( MID( [BasicMeasureString]; 4; 2 ) )    //returns 32
LastPartOfString:=VALUE( MID( [BasicMeasureString]; 7; 2) )          //returns 36

As we see, we only do step „GroupByRegionOrderDate" once. This way we make our calculation much faster. If our final results are numbers, we could get results even faster by using mathematical calculations to extract our values, instead of doing string manipulations. In our Basic measure we could combine three values into one value like this:

VAR Result = BiggerThen75 * 10000 + BiggerThen53 * 100 + BiggerThen27  //returns 253236

Then, we can extract separate numbers by using INT and MOD functions like this:

Dates with sale over75 := INT( [BasicMeasure] / 10000 ) //returns 25
Dates with sale over53 := INT( MOD( [BasicMeasure]; 10000 ) / 100 ) //returns 32
Dates with sale over27 := MOD( [BasicMeasure]; 100 ) //returns 36

Solution with math calculations would be significantly faster.

Sample file can be downloaded here:

Sometimes different users want pivot table sorted in different ways. If column has full names of people, it is possible to sort column either by using first names or by using last names. An additional requirement is the ability to change the sort order using a slicer.

In the example bellow, we can see that our pivot table can be sorted by "Points" (1), but two other possible sorting orders are by "Last Name" (2) or by "First Name" (3). We will create solution only for this three specific sorting methods. Points will be sorted from highest to lowest, and first and last names will be sorted alphabetically.

Helper Tables

First we have to create table in Power Pivot that will hold values for our slicer (1). We will call this table "SlicerValues".

Second, we have to create table in Power Pivot, that will have columns with sort order for every row (1). We need two such columns, one for last names and one for first names. This table will have name "SortOrdersTable".

General Idea

We know that it is possible to sort some column, in pivot table, by using some other column as sort order. First we have to right click on column which we want to sort, and then we go to Sort > More Sort Options (1). New dialog will open. In this dialog we will select "Descending (A to Z) by:" and then we will choose which column will define our sort order. In the image bellow, we decided that "Sum of Points" column (2) in DESC order is used for sorting. Now, "Full Name" column (3) is not sorted alphabetically, but according to "Sum of Points" column.

We can use mechanism, explained above, to control column "Full Name" sorting order. Idea is to create a measure, with a name "SortMeasure", that will be added to our pivot table (1). This measure values will change according to selection in the slicer (3,4,5). Our column "Full Name" will be set to sort by order defined by "SortMeasure" (2).

Now, when the user selects "First Names" (3), "Last Names" (4) or "Points" (5) in the slicer, column "SortMeasure" values will change (1) and that will sort our column "Full Name" (2). We can see in (3) that column "Full Name" is sorted by first name, in (4) by last name, and in (5) by points. At the end, column "SortMeasure" will be hidden from the user by hiding the whole spreadsheet column.

Creative process

First we have to make relation between "Full Name" columns in "SortOrdersTable" and "Data" table (1). "Data" table is the name of a table which is the base for our pivot (with "Full Name" and "Points" columns). Relation has to be "1:N" in direction "Data" to "SortOrdersTable" (2). This means that in relation dialog "Data" table has to be at the bottom. "Data" table can filter "SortOrdersTable".

Next step is to create our measure. This measure has to be reactive to slicer selection. ALLSELECTED function will return values selected in slicer as a column. With green part of code we will check whether specific individual values in slicer are part of that column. If they are, that means that those terms are selected and we will return TRUE(), otherwise we will return FALSE(). At the end, we will use SWITCH function to decide what values the measure should return. SWITCH function will return values for the first variable ( FirstNamesSelected, LastNamesSelected, PointSelected ) that returns TRUE(). For example, if FirstNamesSelected is FALSE(), and LastNamesSelected is TRUE(), values from "Last Name order" column will be returned. Those are the values that will be used to sort "Full Name" column.

SortMeasure:=
VAR FirstNamesSelected = IF( "First Names" IN ALLSELECTED( SlicerValues[SortBy] ); TRUE(); FALSE() )
VAR LastNamesSelected = IF( "Last Names" IN ALLSELECTED( SlicerValues[SortBy] ); TRUE(); FALSE() )
VAR PointsSelected = IF( "Points" IN ALLSELECTED( SlicerValues[SortBy] ); TRUE(); FALSE() )
VAR Result = SWITCH( TRUE(); FirstNamesSelected; SUM( SortOrdersTable[First Name order] ) 
                           ; LastNamesSelected; SUM( SortOrdersTable[Last Name order] )
                           ; PointsSelected; 10000 - SUM( Data[Points] ))
RETURN Result

All columns with possible result ( "First Name order", "Last Name order", "Points" ) are just wrapped in SUM function, but "Points" column has one more detail. In our measure we will transform all "Points" values into "10000 – Points". This is because, we want ascending sorting for "First Name order" and "Last Name order", but descending sorting for "Points". Because pivot table can only be in ascending or descending order at once, we need this transformation so that ascending ordering of our measure will always correctly sort our pivot table. Values of "Points" and "10000 – Points" are inversely correlated, and this solves our problem.

Descending sorting of "Points" is the same as ascending sorting of a transformation "10000 – Points".

Result

Bellow we can see our results and we can observe a few details. Columns "C" and "G" are hidden (1). This is how we will hide our "SortMeasure" column from the user. In green pivot table (2) we can see that while "SortMeasure" column is in ascending order, important column "Sum of Points" is actually in descending order, as per requirement.

In blue and orange pivot tables, there are two values selected in each slicer (3). It is not possible to force single selection in slicer in Excel without resorting to VBA. To solve this problem, we made our measure so that the first selected value is the one determining sorting order. That is why both blue and orange pivot tables are sorted by "First Names", that is the first value selected in their slicers.

Sample table can be downloaded from here: