cancel
Showing results for
Did you mean:
Regular Visitor

Dynamic Measure Values Based on Slicer Selection with Options that are Not Connected to the Data

Hi there, Everyone!

So, I have a spreadsheet with sales values as shown below:

[Sales]

 Account No. Sales Date Sales Year Sales Amount Branch Department Invoice Number AA0012 1-Jan-15 2015 \$23,043,223.99 B1 R&D 30001 AD5013 3-Mar-15 2015 \$46,378,292.21 B2 Personnel 30002 AA0577 7-Jul-15 2015 \$3,668,282.67 B1 R&D 30003 AA0015 3-Feb-16 2016 \$44,988,324.97 B4 R&D 30004 AS7672 8-Aug-16 2016 \$200,192,927.00 B3 Engineering 30005 AQ5452 12-Dec-16 2016 \$32,434,553.00 B1 Personnel 30006 AB6627 4-Apr-17 2017 \$34,546,411.00 B1 Engineering 30007 AD5013 11-Aug-17 2017 \$465,476,575.00 B3 Engineering 30008

Now, to account for inflation over the years, I require a measure (let's call it "SuMm") which can switch between two (2) options from a slicer, i.e. Unadjusted, Adjusted, using data from another table as shown below to get the sum of the "Sales Amount" column either adjusted or unadjusted for corresponding transactions' years:

 Year Nominal Rate 2000 4.11 2001 4.20 2002 4.43 2003 4.60 2004 4.82 2005 5.00 2006 5.20 2007 5.40 2008 5.60 2009 5.80 2010 6.00 2011 6.20 2012 6.70 2013 6.60 2014 6.80 2015 7.00 2016 7.20 2017 7.40 2018 7.33 2019 6.90

For example, if choose "Adjusted", the measure would have the following arithmetic breakdown for the example above:

SuMm = ( 23043223.99 / 7.00 )+ ( 46378292.21 / 7.00 ) + ( 3668282.67 / 7.00 ) + ( 44988324.97 / 7.20 ) + ( 200192927 / 7.20 ) + ( 32434553 / 7.20 ) + ( 34546411 / 7.40 ) + ( 465476575 / 7.40 )

If I choose "Unadjusted" instead, SuMm would be the regular sum, i.e. "SUM( 'Sales'[Sales Amount] )".

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

Re: Dynamic Measure Values Based on Slicer Selection with Options that are Not Connected to the Data

@jmeccles

Hi, Try with this:

1. Create a calculated column in Sales Table:

```SalesAmountWAdjustment =
DIVIDE (
Sales[Sales Amount],
LOOKUPVALUE (
)
)```

2. Create a New Table --Using Enter Data

3. Insert a Slicer Visual With Options Field

4. Finally Create a Measure :

```Measure =
IF (
SELECTEDVALUE ( Options[Options] ) = "Adjusted",
SUM ( Sales[Sales Amount] )
)```

5. Insert this measure in a Card Visual or another that you desire.

Regards

Victor

Lima - Peru

Lima - Peru

Proud to be a Datanaut!

2 REPLIES 2
Highlighted
Super User

Re: Dynamic Measure Values Based on Slicer Selection with Options that are Not Connected to the Data

@jmeccles

Hi, Try with this:

1. Create a calculated column in Sales Table:

```SalesAmountWAdjustment =
DIVIDE (
Sales[Sales Amount],
LOOKUPVALUE (
)
)```

2. Create a New Table --Using Enter Data

3. Insert a Slicer Visual With Options Field

4. Finally Create a Measure :

```Measure =
IF (
SELECTEDVALUE ( Options[Options] ) = "Adjusted",
SUM ( Sales[Sales Amount] )
)```

5. Insert this measure in a Card Visual or another that you desire.

Regards

Victor

Lima - Peru

Lima - Peru