Problem in getting % change values

[amansinghfirstb]

This has been one harrowing tale where so far I have tried every trick in the book to get the answer but nothing has worked so far.  I want something like column F( which uses created column G). The highlighted cells are given and I need column F through DAX.

column F calculates the % order change in the current week wrt the average order for the previous weeks. As you can see

[DataInsights]

@amansinghfirstb,

Try these measures:

Total Qty = SUM ( Orders[Qty] )

% Change = 
VAR vShipWk =
    MAX ( Orders[Schedule Ship Wk] )
VAR vDistinctShipWk =
    ALL ( Orders[Schedule Ship Wk] )
VAR vPrevShipWk =
    FILTER ( vDistinctShipWk, Orders[Schedule Ship Wk] < vShipWk )
VAR vPrevShipWkQty =
    ADDCOLUMNS ( vPrevShipWk, "TotalQty", [Total Qty] )
VAR vAverage =
    AVERAGEX ( vPrevShipWkQty, [TotalQty] )
VAR vResult =
    DIVIDE ( [Total Qty] - vAverage, vAverage )
RETURN
    vResult

[amansinghfirstb]

This is not coming out right.

[DataInsights]

@amansinghfirstb,

Line 9 should use [Total Qty].

Line 11 should use [TotalQty] (no space).

It's best practice not to precede measures with a table name.

[amansinghfirstb]

Thanks, I did the correction, but why am i getting the total like this?

Order Fluctuation% change as 108.81% doesn't make sense. Can I remove it?

[DataInsights]

@amansinghfirstb,

Replace the last line of the measure with this so it will display blank for the total row:

IF ( HASONEVALUE ( Orders[Schedule Ship Wk] ), vResult, BLANK() )

[amansinghfirstb]

@DataInsights 

I noticed something off here. When I put Business column back in the table the order fluctuation goes for a toss. 

Ideally, all the businesses with common week number should have same order fluctuation % as it is being calculated wrt to the total of the week.

[DataInsights]

@amansinghfirstb,

Would you be able to provide the expected result and how it is calculated? You can paste the table visual into Excel and create formulas that show the calculation logic.

[amansinghfirstb]

Business Unit	Ordered Item	Qty	Qty Fulfilled	Schedule Ship Wk	% change
ASC	WP-9938	39	39	1	0
FRAM	BWP2118SP	298	298	1	0
FRAM	BWP2157BR	34	34	1	0
ASC	WP-373	198	198	2	941%
ASC	WP-490HD	32	32	2	941%
ASC	WP-592	40	40	2	941%
ASC	WP-601	601	601	2	941%
ASC	WP-657	411	411	2	941%
ASC	WP-715HD	328	328	2	941%
ASC	WP-775	859	859	2	941%
ASC	WP-853	416	416	2	941%
ASC	WP-9100	90	90	2	941%
ASC	WP-9240	704	704	2	941%
ASC	WP-9829	22	22	2	941%
ASC	WP-9861	44	44	2	941%
ASC	WP-HD6301	98	98	2	941%
FRAM	BWP2056BR	1	1	2	941%
FRAM	BWP2056GP	4	4	2	941%
FRAM	BWP2095GP	3	3	2	941%
FRAM	BWP2115BR	2	2	2	941%
FRAM	BWP2195BR	2	2	2	941%
FRAM	BWP2422BR	6	6	2	941%
ASC	WP-1106	75	75	3	282%
ASC	WP-1983	406	406	3	282%
ASC	WP-2067	107	107	3	282%
ASC	WP-2093	35	35	3	282%
ASC	WP-2221	267	267	3	282%
ASC	WP-2271	406	406	3	282%
ASC	WP-2378	154	154	3	282%
ASC	WP-2468	600	600	3	282%
ASC	WP-2684	117	117	3	282%
ASC	WP-366HDA	84	84	3	282%
ASC	WP-373HDP	18	18	3	282%
ASC	WP-413HDA	6	6	3	282%
ASC	WP-595	140	140	3	282%
ASC	WP-645	189	189	3	282%
ASC	WP-661	140	140	3	282%
ASC	WP-726	660	660	3	282%
ASC	WP-836	160	160	3	282%
ASC	WP-853	336	336	3	282%
ASC	WP-888	94	94	3	282%
ASC	WP-9046	450	450	3	282%
ASC	WP-9164	172	172	3	282%
ASC	WP-9225	437	437	3	282%
ASC	WP-9361	1320	1320	3	282%
ASC	WP-9408	216	216	3	282%
ASC	WP-9414	490	490	3	282%
ASC	WP-9839	72	72	3	282%
ASC	WP-9860-EA	402	402	3	282%
ASC	WP-9933	40	40	3	282%
ASC	WP-9939	5	5	3	282%
ASC	WP-HD6073	140	140	3	282%
ASC	WP-TM27K6105	27	27	3	282%
Carter	M60318B-0101	188	188	3	282%
FRAM	BWP2556BR	9	9	3	282%
FRAM	BWP511SP	2	2	3	282%
FRAM	BWP9240DG	43	43	3	282%
FRAM	WP462 SP	20	20	3	282%
FRAM	WP635 BLANCA	1	1	3	282%
FRAM	WP635 SP	55	55	3	282%

% change =abs( sum of order quantitiy for a particular week- average of order quantity for all previous weeks)/average of order quantity for all previous weeks

Your code works well for outer filters, but when I add fields inside the table it starts giving weird results. I have 15 more fields that I want users to slice/dice the data on. This % change data will only depend on the week selected.

[DataInsights]

@amansinghfirstb,

I get the same % change as you show (941% and 282%), with Business Unit displayed, as well as Business Unit not displayed. Would you post the % change measure so I can see your DAX?

[amansinghfirstb]

I am pulling Total Qty1 from this measure I defined.

[DataInsights]

@amansinghfirstb,

Line 11 of your measure is wrong.

Line 11 should use [TotalQty] (no space). This is a temporary column that is created for calculation purposes.

[amansinghfirstb]

Total Qty = SUM ( Orders[Qty] )

@DataInsights Did you mean this?

How would you put this in the same measure of order % fluctuation?

[DataInsights]

@amansinghfirstb,

Try this measure. I rewrote it using your table/column names, and modified the logic to handle different granularities.

Order Fluctuation % change = 
VAR vShipWk =
    MAX ( 'All Fill Rate'[Wk] )
VAR vDistinctShipWk =
    ALLSELECTED ( 'All Fill Rate'[Wk] )
VAR vTotalQty =
    CALCULATE (
        SUM ( 'All Fill Rate'[Qty] ),
        ALLEXCEPT ( 'All Fill Rate', 'All Fill Rate'[Wk] )
    )
VAR vPrevShipWk =
    FILTER ( vDistinctShipWk, 'All Fill Rate'[Wk] < vShipWk )
VAR vPrevShipWkQty =
    ADDCOLUMNS (
        vPrevShipWk,
        "tmpTotalQty",
            CALCULATE (
                SUM ( 'All Fill Rate'[Qty] ),
                ALLEXCEPT ( 'All Fill Rate', 'All Fill Rate'[Wk] )
            )
    )
VAR vAverage =
    AVERAGEX ( vPrevShipWkQty, [tmpTotalQty] )
VAR vResult =
    DIVIDE ( vTotalQty - vAverage, vAverage )
RETURN
    IF ( HASONEVALUE ( 'All Fill Rate'[Wk] ), vResult, BLANK () )

[amansinghfirstb]

Still no success. Infact, even the aggregate values without internal filters are coming out wrong.

Also, why did you initialize the total qty with sum for all qty except the selected week? (Line 7,8) Doesn't make sense. 

[DataInsights]

@amansinghfirstb,

The variable vTotalQty (lines 6-9) uses ALLEXCEPT in order to remove the filter criteria from all columns except [Wk]. You need to keep the [Wk] filter (from the current row), but ignore filters from Business Unit, and any other columns you add to the visual.

If you could upload a sanitized version of your pbix, I'll take a look.