Forum Discussion

amansinghfirstb's avatar
amansinghfirstb
Helper III
5 years ago

Problem in getting % change values

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 ...
Need Help
amansinghfirstb's avatar
amansinghfirstb
Helper III
5 years ago
Total Qty = SUM ( Orders[Qty] )

DataInsights Did you mean this?

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

DataInsights's avatar
DataInsights
Super User
5 years ago

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's avatar
    amansinghfirstb
    Helper III
    5 years ago

     

    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's avatar
    DataInsights
    Super User
    5 years ago

    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.