cancel
Showing results for
Did you mean:
Highlighted
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
Super Contributor

## 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!

3 REPLIES 3
Super Contributor

## 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!

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.

New Member

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

Hi,

Does anyone have any advice on how to develop a measure that runs from many slicers, with each slicer having more than 10+filters?

I have attached an image where I would like to know the total count based on the customer, location, property type, type of customer, cost category selected in the multiple slicers. Also the option where if nothing is selected on the slicer, then it just counts all cells.

In the solution above, the filter in the slicer is written in the measure. Unfortunately that does not work for me, because in reality I have a load more options than the few in the image, so want to avoid writing these filters in the formula.

Hope this makes sense! Any help will be much appreciated.

Thanks

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 317 members 3,428 guests
Recent signins: