Forum Discussion

Lanceometer's avatar
Lanceometer
Helper 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%.

I am doing this for every my materials and aggregate an average to show this aggregated line.

 

So in short I want to do this:

per Material divide the price by the first price

 

My Dax formula looks like this:

Freight Surcharge Index =

AVERAGEX(

    VALUES(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Material No])

    , 

    DIVIDE(

        SUM(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge]) 

        ,

        CALCULATE(

            // Here it ignores the VALUES Grouping

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

            ,ALLSELECTED(DIM_Calendar[Date])

        )

    )

)
 
unfortunately in the CALCULATE part it seems to ignore the grouping by [Material No]
Propably because of the ALLSELECTED(DIM_Calendar[Date])
But I need this to excape the Date Dimension in the chart and get the first value.
 
How can I escape the Date Dimension in the Chart but keep the grouping of [Material No]?

 

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!

     

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Lanceometer , Try using

    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(
    FIRSTNONBLANKVALUE(
    AML_PRICE_DATA_FACT_PURCHASING_PRICES[Report Dt],
    SELECTEDVALUE(AML_PRICE_DATA_FACT_PURCHASING_PRICES[Freight Surcharge])
    ),
    REMOVEFILTERS(DIM_Calendar[Date])
    )
    )
    )

    • Lanceometer's avatar
      Lanceometer
      Helper I
      1 year ago

      Thank you for your suggestion.

      Unfortunately the result is the same

      • bhanu_gautam's avatar
        bhanu_gautam
        Super 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])
        )
        )
        )