Forum Discussion

Lanceometer's avatar
Lanceometer
Icon for Helper I rankHelper I
1 year ago
Solved

ALLSELECTED but include other filter

Hi Folks,   I have a problem with the VALUES() fuction.   I am creating a linechart showing a price-index. So the chart always starts at 1 and e.g. goes down to 0.98 when the price drops by 2%. ...
ALLSELECTED
DAX
values
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Lanceometer , Try using EARLIER than

     

    dax
    Freight Surcharge Index =
    AVERAGEX(
    VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Material No]),
    DIVIDE(
    SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge]),
    CALCULATE(
    FIRSTNONBLANK(
    AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge],
    AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt]
    ),
    REMOVEFILTERS(DIM_Calendar[Date]),
    VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Material No])
    )
    )
    )

  • Lanceometer's avatar
    Lanceometer
    1 year ago

    Adding VALUES() to the Calculate part did the trick!

     

    So now the correct formula is:

     

    Freight Surcharge Index = 

    AVERAGEX(

        VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Granularity])

        ,

        DIVIDE(

            SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge])

            ,

            CALCULATE(

                FIRSTNONBLANKVALUE(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt],SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge]))

                ,

                ALLSELECTED(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt])

                , VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Granularity])

            )

        )

    )
     
    which results in this



    Thank you!

     
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Lanceometer , Try using EARLIER than

 

dax
Freight Surcharge Index =
AVERAGEX(
VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Material No]),
DIVIDE(
SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge]),
CALCULATE(
FIRSTNONBLANK(
AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge],
AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt]
),
REMOVEFILTERS(DIM_Calendar[Date]),
VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Material No])
)
)
)

Lanceometer's avatar
Lanceometer
Icon for Helper I rankHelper I
1 year ago

Adding VALUES() to the Calculate part did the trick!

 

So now the correct formula is:

 

Freight Surcharge Index = 

AVERAGEX(

    VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Granularity])

    ,

    DIVIDE(

        SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge])

        ,

        CALCULATE(

            FIRSTNONBLANKVALUE(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt],SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge]))

            ,

            ALLSELECTED(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt])

            , VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Granularity])

        )

    )

)
 
which results in this



Thank you!