cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create measure using factors from table.

Hi! 

 

I´ve created this table containing all my my processes and corresponding factors. I want to use factors for each process beacuse when im forecasting i tend to see trends changing on process level. 

 

So I´ve created my measure: 

 

"ForeCastWithFactors = [Forecast] * CALCULATE(SUMX('ProcessFactors'; 'ProcessFactors'[ProcessFactor]))"

 

and row by row it works as expected, it increases the forecast by the factor (they are all 1.07 at the time) 

However the problem is the total. its Obviously calculating the sum of all the factors x sum of forecast. and thus its not really usable in a bar chart for example. 

 

Anyone know how to rewrite this so it works? 

 

I did make a post about this earlier: https://community.powerbi.com/t5/Desktop/Measure-with-a-factor-table/m-p/421672#M193883

  

I did get help but the measure does not fill my entire calendar. Only until the end of april (Where my current data ends) 

 

My measure for "Forecast" is created like this: 

 

Measure: Result2016 = CALCULATE([Total Offered]; DATEADD('Dates'[Date]; -728; DAY))

Measure: Result2017 = CALCULATE([Total Offered]; DATEADD('Dates'[Date]; -364; DAY))

 

I´ve mixed with the number of days to get the weekends to line up for this year. 

 

Measure: Forecast =

Var D1 = [Result2016]
Var D2 = [Result2017]

RETURN
DIVIDE(D1 + D2; 2; 0)

 

The proposed solution was this one and it does work like a charm and does what it is expected to do. Except that it seems to calculate only until my last record in April, see last picture. 

 

Measure: ForeCastWithFactors =  SUMX('ProcessFactors'; [forecast] * ‘ProcessFactors'[ProcessFactor])

 

 

 

 

 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Create measure using factors from table.

Hey,

I do not totally understand your requirement, but you may start with this:
ForeCastWithFactors =
CALCULATE(
SUMX('ProcessFactors'[Process],
'ProcessFactors'[ProcessFactor]* [Forecast]
)
)

This creates the product for each Row filtered by the Provess

Hopefully this will get you started



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Frequent Visitor

Re: Create measure using factors from table.

Hi! Sorry for my bad english, hopefully this will clear things up a bit. 

 

My current problem is this measure: 

 

ForeCastWithFactors = SUMX('ProcessFactors'; [forecast] * 'ProcessFactors'[ProcessFactor]) It calculates correctly but it does not 

fill my entire calendar for some reason. It only calculates until the end of April. I have no actual data after April. 

 

My first measure "Forecast" however works fine and fills my entire calendar.

 

Measure: Forecast =

Var D1 = [Result2016]
Var D2 = [Result2017]

RETURN
DIVIDE(D1 + D2; 2; 0)

 

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors