Share via

Power Pivot: Calculating the % Difference Between the Same Measure Evaluated in Two User-Selected Contexts

  • Article

 

The Problem

A forum user needed to find a way to use the values in a pivot table report filter, to act as variables in Power Pivot-based calculated fields. They had the following columns in their Power Pivot table: |location_id|plan_year|plan_id|dollars|units|date|

Where there are multiple plan_ids for a given plan_year.

They needed to create a comparison-type report, where they could compare any arbitrary plan_id against another, but didn’t want to go through the dirty work of having to create a calculated field for each plan_id. The user wanted the solution to be flexible enough to achieve  the following:

|plan_id_9|Plan_id_10|%difference|

Where the two plan_ids are a report filter, and in the calculated field, it interprets the two values as filters in the equation automatically.

The Solution

The mocked-up data used for this example can be seen in the table below. I will refer to this table as 'plandetail'. References to 'plan1' and 'plan2' correspond to the tables that your filter selection 1 and filter selection 2 will be placed on respectively.

1. Firstly, we add two 'plan' lookup tables that have the distinct list of plan id's. One is to be used as a lookup for the 'plandetail' table i.e. 'plandetail'[planid] -> 'plan'[planid]. This is effectively the 'plan1'. This relationship should be defined as 'active'.

2. Next we, we need to create another lookup going from the first 'plan' lookup table to a second 'plan' lookup table i.e. 'plan'[planid] -> 'plan2'[planid]). This time the relationship should be defined as 'inactive' because it should have no effect on the 'plandetail' table without being explicitly activated in a DAX formula.

3. Define DAX formulas for the measures that need to be compared using the following approach. I have already created a simple base measure called 'SumOfDollars' which is defined as follows:

SumOfDollars:=CALCULATE(SUM(PlanDetail[dollars]))

For 'plan1' the calculation is simply aliasing the measure above since we already know, that by default, the measure will be evaluated within the context of the 'plan1' table filters:

SumOfDollarPlan1:=[SumOfDollars]

The formula for 'plan2' is a bit more interesting:

SumOfDollarPlan2:=
CALCULATE(
  [SumOfDollars], 
  ALL('Plan'), 
  USERELATIONSHIP(
    Plan[plan_id], Plan2[plan_id]
  )
)

What this does is to clear the filter propagated to the 'plandetail' table by the 'plan1' table, and then apply the filter placed on the 'plan2' table, which in turn re-filters the 'plan1' table by the 'plan2' selection, which in turn filters the 'plandetail' table by the 'plan2' selection. The [SumOfDollars] measure is then calculated within the context of the 'plan2' filter.

4. After defining the above measures, we get one number calculated for SumOfDollarsPlan1 (i.e. plan id selection 1) and a different number calculated for the SumOfDollarsPlan2 (i.e. plan id selection 2), assuming that the 'plan1' planid selection is different from the 'plan2' planid selection.

You can then subtract one from the other and calculate the percentage difference:

% Difference:=DIVIDE([SumOfDollarPlan1] - [SumOfDollarPlan2], [SumOfDollarPlan2])

As with any technology, there are several ways to solve the same problem. Can you think of a different solution? Let me know in the comments below.