Calculating order quantity change wrt to average of previous weeks

[amansinghfirstb]

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

[v-janeyg-msft]

Hi, @amansinghfirstb 

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.

If it doesn’t solve your problem, please feel free to ask me.

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.

[amansinghfirstb]

Hi Jeny,

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

[v-janeyg-msft]

Hi, @amansinghfirstb 

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

[amansinghfirstb]

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

[v-janeyg-msft]

Hi, @amansinghfirstb 

Try this:

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

RETURN DIVIDE(SELECTEDVALUE(Table1[Qty])-averageqty,averageqty)

If it doesn’t solve your problem, please feel free to ask me.

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.

[amansinghfirstb]

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%

[v-janeyg-msft]

Hi, @amansinghfirstb 

The 'UnitQty' column is aggregate data?

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

If it doesn’t solve your problem, please feel free to ask me.

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.

[amansinghfirstb]

Hi @v-janeyg-msft , 

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

Please help me on this one.

[v-janeyg-msft]

Hi， @amansinghfirstb 

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 )

If it doesn’t solve your problem, please feel free to ask me.

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.

[amansinghfirstb]

Hi @v-janeyg-msft ,

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.

[v-janeyg-msft]

Hi, @amansinghfirstb 

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.

[amansinghfirstb]

Hi @v-janeyg-msft ,

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 ))

[amansinghfirstb]

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

[amansinghfirstb]

@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)

[amitchandak]

@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)

[amansinghfirstb]

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.