cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmeccles Regular Visitor
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:

 

[Adjustment Rates]

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] )".

Could you please help me out? Is such a method possible and if it is, how would you go about creating it?

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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 (
        'Adjustment Rates'[Nominal Rate],
        'Adjustment Rates'[Year], Sales[Sales Year]
    )
)

SalesImg.png

 

2. Create a New Table --Using Enter Data

 

Img2.png

 

3. Insert a Slicer Visual With Options Field

 

4. Finally Create a Measure :

 

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

 

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

 

adjusted.gif

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
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 (
        'Adjustment Rates'[Nominal Rate],
        'Adjustment Rates'[Year], Sales[Sales Year]
    )
)

SalesImg.png

 

2. Create a New Table --Using Enter Data

 

Img2.png

 

3. Insert a Slicer Visual With Options Field

 

4. Finally Create a Measure :

 

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

 

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

 

adjusted.gif

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jmeccles Regular Visitor
Regular Visitor

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

This totally works for me!

Thank you very much, Victor.