Forum Discussion
Problem in getting % change values
| 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.
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?
I am pulling Total Qty1 from this measure I defined.
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.
Total Qty = SUM ( Orders[Qty] )
DataInsights Did you mean this?
How would you put this in the same measure of order % fluctuation?
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 () )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.
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.