cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmartiprev
New Member

Changing Data through slicer

I would like to create new data based on a slicer setting i have chosen linked to a parameter values.  For example, i have a baseline monthly forecast for 2022.  I would like to apply a percentage change to this baseline forecast for alternative scenarios.  For instance, i want to see what the forecast looks like if it was 5% higher than the baseline forecast.  I need this at the data column level and not a measure so that i can create some formulas off that new column data.  For instance, if column B is the baseline forecast, I have create a new column that equals the baseline forecast column multiplied by (1+ parameter value); in this case, i set my slicer to 5%.  I could have easily chosen 6% or something else, that's the type of functionality I'd like to build. 

I have created a measure that works visually for this, but i realized I really need this at the column level to use it for other things the way I want.

 

Adjusted Forecast = 'sheet1'[Baseline Forecast]*(1+'Adjustment Parameter'[Adjustment Parameter Value]/100)
 
Adjustment Forecast.PNG
4 REPLIES 4
dmartiprev
New Member

Is it possible to create a new data table in Power BI using this measure you just created so i can get it at a base level using the summarizecolumn function?  = SUMMARIZECOLUMNS('Sheet1'[Date],"Original Hours",average('sheet1'[Baseline Forecast]),"Adj Hours", 'sheet'[Adjusted Forecast])

 

I need to get the adjusted forecast at a base level so can plug this adjusted forecast number (hours) into a formula that calculates a new sales forecast by date that i can sum all the date sales forecasts into a monthly, quarterly, or annual sales forecast.  If I end up not getting the adjusted hours forecast at the base date level, the measure is either summing or averaging all dates' hours which will not work for my formula.  I need the sales formula to calculate an answer for each date that i can later sum, not come up with an answer right away because i don't what the measure you created ends up giving me.

dmartiprev
New Member

I'm sorry this is not a solution that works for what I am doing.  This could work if it generated a new table with base data that changes when we adjust the slicer settings.  I need to build a formula at the base level that i can then sum all the individual period solutions into a total.  When i use a measure it either sums or averages the solulutions  for all periods for the formula which gives a incorrect answer.

v-yangliu-msft
Community Support
Community Support

Hi  @dmartiprev ,

Here are the steps you can follow:

1. Create calculated table.

Table 2 = GENERATESERIES(0,1,0.01)

vyangliumsft_0-1645168008919.png

2. Create measure.

Adjusted Forecast =
var _select=SELECTEDVALUE('Table 2'[Value])
var _sum=SUMX('Table','Table'[Baseline Forcast])
return
 _sum*( 1+_select)

3. Result:

Take [Value] as the slicer.

vyangliumsft_1-1645168008932.png

 

Best Regards,

Liu Yang

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

amitchandak
Super User
Super User

@dmartiprev , You can not use slicer value in a new column, you have to create a measure using sumx to have line-level values

 

Adjusted Forecast =Sumx( 'sheet1', 'sheet1'[Baseline Forecast]*(1+[Adjustment Parameter Value Measure]/100) )

 

assuming Adjustment Parameter is what if parameter - https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors