cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amansinghfirstb
Helper III
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 UnitsQtyScheduled Wk
ASCE35001
ASCE45002
ASCE4003

 

16 REPLIES 16
v-janeyg-msft
Community Support
Community Support

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.

v-janeyg-msft_0-1603073523533.png

v-janeyg-msft_0-1603074074652.png

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.

Hi Jeny,

 

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

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

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

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)

v-janeyg-msft_0-1603110073049.png

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.

It is stil throwing up weird numbers.

Business UnitQtySchedule Ship Wkcurr/average
ASCE391 
ASCE38432-100.00%
ASCE77653-100.00%
ASCE43574-100.00%
ASCE334325-100.00%
ASCE377147-100.00%

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.

 

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.

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 )

v-janeyg-msft_0-1603431349509.png

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.

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.

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.

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.

amansinghfirstb_0-1603726069515.png

 

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

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

Business UnitOrdered ItemQtySchedule Ship Wk
ASCEWP-993890391
ASCEWP-373451982
ASCEWP-490HD45322
ASCEWP-59243402
ASCEWP-6016012

 

The solution provided above still throws an error in calculation

@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
Super User IV
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)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors