cancel
Showing results for
Did you mean:
Helper III

## Calculating order quantity change wrt to average of previous weeks

I am having trouble understanding the problem in the code for a simple feature of finding order quantity fluctuation % wrt to the average demand of the selected previous weeks (selected through filter).

The Qty column is a rolled-up number and it has many instances of Scheduled Wk, but I want to capture the average of the rolledup summaries like this table shown below.

In the table

 Business Units Qty Scheduled Wk ASCE 3500 1 ASCE 4500 2 ASCE 400 3

16 REPLIES 16
Community Support

It’s my pleasure to answer for you.

According to your description, I think you can create two measure to calculate average Qty and the percentage.

Like this:

Measure:

``````averageqty = AVERAGEX(FILTER((Table1),[Scheduled Wk]<=MAX([Scheduled Wk])),[Qty])

curr/average = DIVIDE(MAXX(FILTER(Table1,[Scheduled Wk]=MAX(Table1[Scheduled Wk])),[Qty]),[averageqty])``````

Or you can combine two measures into one.

Best Regards

Janey Guo

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

Helper III

Hi Jeny,

Can you explain what you did? Your solution is giving a wrong result.

Community Support

I'm not very clear about what you really want to calculate and what the visual form of your desired result is like.Could you please provide us with some further information.

Best Regards

Janey Guo

Helper III

I need the fluctuation in order quantity over the period of time.

For example, in week 3, the fluctuation would be [400 - average of (3500 and 4500)]/average(3500 and 4500) !

Community Support

Try this:

curr/average = VAR averageqty = AVERAGEX(FILTER(ALL(Table1),[Scheduled Wk]<SELECTEDVALUE(Table1[Scheduled Wk])),[Qty])
RETURN DIVIDE(SELECTEDVALUE(Table1[Qty])-averageqty,averageqty)

Best Regards

Janey Guo

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

Helper III

It is stil throwing up weird numbers.

 Business Unit Qty Schedule Ship Wk curr/average ASCE 39 1 ASCE 3843 2 -100.00% ASCE 7765 3 -100.00% ASCE 4357 4 -100.00% ASCE 33432 5 -100.00% ASCE 37714 7 -100.00%
Community Support

The 'UnitQty' column is aggregate data?

Try to change 'selectedvalue([qty])' to sum[qty]. Please give me further information.

Best Regards

Janey Guo

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

Helper III

I did a sample calculation, wherein I want power bi to fork out % change in order quantity (shown in column F) based on the filters applied.

I am using the formula: % change = [sum(demand for the week)- average (weekly demand before this week)]/ average (weekly demand before this week).

Community Support

Try this:

``````curr/average =
VAR a =
SUMX (
FILTER (
ALL ( Table1 ),
[Scheduled Wk] < SELECTEDVALUE ( Table1[Scheduled Wk] )
),
[Qty]
)
VAR b =
DIVIDE ( a, SELECTEDVALUE ( Table1[Scheduled Wk] ) - 1 )
VAR c =
SUMX (
FILTER (
ALL ( Table1 ),
[Scheduled Wk] = SELECTEDVALUE ( Table1[Scheduled Wk] )
),
[Qty]
)
RETURN
DIVIDE ( c - b, b )``````

Best Regards

Janey Guo

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

Helper III

I did what you mentioned but the values I am getting are not changing according to filters applied. For example, if I apply business filter on column A I would expect the measure to reflect accordingly, but that's not happening.

Community Support

Try to change 'all(table)' to ‘allselected(table)’ in the preivous formula.

Best Regards

Janey Guo

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

Helper III

That solution is working good in parts. For example, if all the weeks are selected then it gives the right breakup, but if I select just two or three weeks out of 52 weeks then it gives a wrong result.

This is the code

Order_Fluc_Wkly% =
VAR a =
SUMX (
FILTER (
ALLSELECTED ( 'TMM Data' ),
[Schedule Ship Wk] < MAX ( 'TMM Data'[Schedule Ship Wk] )
),
[Qty]
)
VAR b =
DIVIDE ( a, MAX ( 'TMM Data'[Schedule Ship Wk] ) - 1 )
VAR c =
SUMX (
FILTER (
ALLSELECTED ( 'TMM Data' ),
[Schedule Ship Wk] = MAX ( 'TMM Data'[Schedule Ship Wk] )
),
[Qty]
)
RETURN
ABS ( DIVIDE ( c - b, b ) )

VAR b =
DIVIDE ( a, MAX( 'TMM Data'[Schedule Ship Wk] ) - 1 )
VAR c =
SUMX (
FILTER (
ALLSELECTED('TMM Data'),
[Schedule Ship Wk]=MAX( 'TMM Data'[Schedule Ship Wk])
),
[Qty]
)
RETURN
ABS(DIVIDE ( c - b, b ))
Helper III

Yes, I have already stated in the question that it's a rolled up data.

 Business Unit Ordered Item Qty Schedule Ship Wk ASCE WP-993890 39 1 ASCE WP-37345 198 2 ASCE WP-490HD45 32 2 ASCE WP-59243 40 2 ASCE WP-601 601 2

The solution provided above still throws an error in calculation

Helper III

@Greg_Deckler  need your help!! I did read your book where you summarized OTIF by order level, but when I try doing this summarization by scheduled week I am not getting the desired result.

Measure =

VAR __Thisweek = MAX('TMM Data'[Schedule Ship Wk])

VAR __ThisweekYear = MAX('TMM Data'[Schedule Ship Yr])

VAR __ThisweekQty =SUM('TMM Data'[Qty])

VAR __Lastweek = IF(__ThisMonth = 1 ,52 ,__Thisweek - 1)

VAR __LastweekYear = IF(__ThisMonth = 1, __ThisweekYear - 1 ,

__ThisweekYear)

VAR __SUMMARIZEDTHISweek =

SUMMARIZE(FILTER(

'TMM Data',

'TMM Data'[Schedule Ship Wk]=__Thisweek),[Schedule Ship Wk],

"__SUMMARIZEDTHISweek",

SUM('TMM Data'[Qty])

)

VAR __LastMonthsavgQty =SUMMARIZE(

FILTER(

'TMM Data',

'TMM Data'[Schedule Ship Wk]<=__Lastweek),[Schedule Ship Wk]," __LastMonthsavgQty ",

sum('TMM Data'[Qty]))

RETURN

DIVIDE(__SUMMARIZEDTHISweek -__LastMonthsavgQty, __LastMonthsavgQty,0)

Super User IV

@amansinghfirstb , It better to have a separate Week/ Date table in such case as you might require all in filter. Separated out formula for better understanding. You can combine

This Week = CALCULATE(
SUM('Data'[Qty]),
FILTER('Data','Data'[Scheduled Wk]=MAX('Data'[Scheduled Wk])))

last Week =CALCULATE(
SUM('Data'[Qty]),
FILTER('Data','Data'[Scheduled Wk]=MAX('Data'[Scheduled Wk])-1))

or With week table

This Week = CALCULATE(
SUM('Data'[Qty]),
FILTER(all('Week'),'Week'[Scheduled Wk]=MAX('Week'[Scheduled Wk])))

last Week =CALCULATE(
SUM('Data'[Qty]),
FILTER(all('Week'),'Week'[Scheduled Wk]=MAX('Week'[Scheduled Wk])-1))

% Change =
ABS(DIVIDE([This WeeK] - [Last WeeK],0) -1)

Proud to be a Super User!

Helper III

I actually forgot to mention that I am looking for % change in the demand of the current week wrt the average of all other weeks' demand selected from the filter.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Check out how to claim yours today!

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors