Percentage Difference with a Slicer that effects the 'filtered value' only

[Anonymous]

Hi everyone,

I'm using PowerBI's handy quick measure 'percentage difference from filtered value'. I have 25 years worth of data, and the 'filtered value' is my benchmark year.

At the moment, I have to create a measure for each benchmark year - 2015, 2018, and 2020 for example. What I would like to do is allow the user to select the benchmark themselves.

However, when they do this, I don't want the slicer to impact any other visuals on the page.

For example: My measures shows the percentage difference between the year 2025 and the benchmark year 2015. I want the user to be able to change the benchmark year from 2015 to say 2018. However, when I try to create slicers to do this, it also changes the year 2025 to 2018 - thereby giving my a value of 0. I want 2025 to stay the same, only the benchmark year to change.

Here is an example of how the data is laid out (repeated for 10 years' worth of data, where entries in the changing value): 

(To note: I have also tried adding a two year columns, and creating a new table with a year column, to try and filter using only that slicer, but the result is still the same for some reason). 

Code	Course	Applicants (3years)	Feeder Number	Benchmark Year	Entry Type	Entries
G7	AI	1		2025	Entries	200
G7	AI	1		2025	At Tariff	300
G7	AI	1		2025	PEAT	400
G7	AI	1		2025	PE	200
G7	AI	1		2025	Entries	150
G7	AI	1		2025	At Tariff	350
G7	AI	1		2025	PEAT	2650
G7	AI	1		2025	PE	31
G7	AI	1		2025	Entries	56
G7	AI	1		2025	At Tariff	230
G7	AI	1		2025	PEAT	156
G7	AI	1		2025	PE	13
G4	CS	1		2025	Entries	12
G4	CS	1		2025	At Tariff	515
G4	CS	1		2025	PEAT	89
G4	CS	1		2025	PE	1500
H6	CS	1		2025	Entries	600
H6	CS	1		2025	At Tariff	200
H6	CS	1		2025	PEAT	300
H6	CS	1		2025	PE	100
H6	CS	1		2025	Entries	100
H6	CS	1		2025	At Tariff	100
H6	CS	1		2025	PEAT	200
H6	CS	1		2025	PE	250
G7	AI	1		2018	Entries	250
G7	AI	1		2018	At Tariff	350
G7	AI	1		2018	PEAT	450
G7	AI	1		2018	PE	250
G7	AI	1		2018	Entries	200
G7	AI	1		2018	At Tariff	400
G7	AI	1		2018	PEAT	2700
G7	AI	1		2018	PE	81
G7	AI	1		2018	Entries	106
G7	AI	1		2018	At Tariff	280
G7	AI	1		2018	PEAT	206
G7	AI	1		2018	PE	63
G4	CS	1		2018	Entries	62
G4	CS	1		2018	At Tariff	565
G4	CS	1		2018	PEAT	139
G4	CS	1		2018	PE	1550
H6	CS	1		2018	Entries	650
H6	CS	1		2018	At Tariff	250
H6	CS	1		2018	PEAT	350
H6	CS	1		2018	PE	150
H6	CS	1		2018	Entries	150
H6	CS	1		2018	At Tariff	150
H6	CS	1		2018	PEAT	250
H6	CS	1		2018	PE	300

The measure I'm currently using:

Entries % difference from 2015 = 

VAR __BASELINE_VALUE =

    CALCULATE(

        SUM('Unpivoted Bubble Chart Master'[Entries]),

        'Unpivoted Bubble Chart Master'[Benchmark Year] IN { 2018 }

    )

VAR __MEASURE_VALUE = SUM('Unpivoted Bubble Chart Master'[Entries])

RETURN

    IF(

        NOT ISBLANK(__MEASURE_VALUE),

        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

And I have one measure per benchmark year I have. The aim is to create a table with all ten years as the columns, with the calculated percent change, and a slicer that only changes the benchmark year in my measure. 

I am wondering if this is just something simple I am missing, or if I need to look at creating a more complex model (happy to share if anyone thinks that is the case). Or if there's something I can place in the data table itself to get this working. 

Thanks!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

For this case, you can modify your measure like this:

Entries % difference from 2015 =

VAR __BASELINE_VALUE =

    CALCULATE (

        SUM ( 'Unpivoted Bubble Chart Master'[Entries] ),

        FILTER (

            ALL ( 'Unpivoted Bubble Chart Master'[Benchmark Year] ),

            'Unpivoted Bubble Chart Master'[Benchmark Year]

                IN {

                2018

            }

        )

    )

VAR __MEASURE_VALUE =

    SUM ( 'Unpivoted Bubble Chart Master'[Entries] )

RETURN

    IF (

        NOT ISBLANK ( __MEASURE_VALUE ),

        DIVIDE (

            __MEASURE_VALUE - __BASELINE_VALUE,

            __BASELINE_VALUE

        )

    )

You will get the following result by sample data:

Here is the demo , please try it:

PBIX 

Best Regards,

Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous it should be pretty straight forward. Create a table with all the benchmark year (unique year) and this table shouldn't have relationship with any other table. Use year from this table on the slicer and in your measure, use selected year  from this table instead of fixed value of 2018 as shown in your measure. Hope it helps.

[Anonymous]

Thanks for the suggestion, unfortunately it didn't work. Even without a relationship between the tables I'm coming up with values like 95.5% which isn't correct.

There may be a few points I missed: The percentage difference is between entries for each course, for each entry type. At the moment, a slicer is used to pick the entry type and which year you would like to see calculated from the benchmark year. So I think a relationship between the tables might be required (or I would need to duplicate a slicer for all of the variables twice for both tables and hope the user clicks on the correct ones).