ALLSELECTED but include other filter

[Lanceometer]

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]?

[bhanu_gautam · Accepted solution]

@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])
)
)
)

[bhanu_gautam]

@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]

Thank you for your suggestion.

Unfortunately the result is the same

[bhanu_gautam · Accepted solution]

@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 · Accepted solution]

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!