Calculate average price based on certain condition

Hi Nun ,

Sorry for the inconvenience and delay response.

Assume that your table name is SalesData:

TotalPZ = CALCULATE(SUM(SalesData[PZ]))

Try to Create a Ranked Table (sorted by EUR/PZ ascending)

RankedTable =
ADDCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            SalesData,
            "EURPerPZ", SalesData[EUR] / SalesData[PZ]
        ),
        NOT ISBLANK(SalesData[PZ]) && SalesData[PZ] > 0
    ),
    "Rank", RANKX (ALL (SalesData), SalesData[EUR] / SalesData[PZ], , ASC, DENSE)
)

You can also pre-create EURPerPZ as a calculated column.)

Next, please use the below DAX to calculate Cumulative PZ and Filter for Top 10%

Top10PctTable =
VAR TotalPZ = CALCULATE(SUM(SalesData[PZ]))
VAR PZTarget = TotalPZ * 0.10
VAR SortedTable =
    ADDCOLUMNS (
        FILTER (
            SalesData,
            NOT ISBLANK(SalesData[PZ]) && SalesData[PZ] > 0
        ),
        "EURPerPZ", SalesData[EUR] / SalesData[PZ]
    )
VAR SortedByPrice =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SortedTable,
           "CumulativePZ",
            SUMX (
                FILTER (
                   SortedTable,
                    (SalesData[EUR] / SalesData[PZ]) <= EARLIER(SalesData[EUR] / SalesData[PZ])
                ),
                SalesData[PZ]
            )
        ),
       "IncludeRow",
        IF (
            SUMX (
                FILTER (
                   SortedTable,
                    (SalesData[EUR] / SalesData[PZ]) <= EARLIER(SalesData[EUR] / SalesData[PZ])
                ),
                SalesData[PZ]
            ) <= PZTarget,
            1,
            0
        )
    )
RETURN
    FILTER (SortedByPrice, [IncludeRow] = 1)

As a final step, create a below measure to calculate Weighted Avg Price for Top 10% PZ:

Top10Pct_EURPerPZ =
VAR TotalPZ = CALCULATE(SUM(SalesData[PZ]))
VAR PZTarget = TotalPZ * 0.10
VAR SortedTable =
    ADDCOLUMNS (
        FILTER (
            SalesData,
            NOT ISBLANK(SalesData[PZ]) && SalesData[PZ] > 0
        ),
        "EURPerPZ", SalesData[EUR] / SalesData[PZ]
    )
VAR Ordered =
    ADDCOLUMNS (
        SortedTable,
        "SortOrder", RANKX(SortedTable, [EURPerPZ], , ASC)
    )
VAR Running =
    ADDCOLUMNS (
        Ordered,
        "RunningPZ",
        SUMX (
            FILTER (
                Ordered,
                [SortOrder] <= EARLIER([SortOrder])
            ),
            SalesData[PZ]
        )
    )
VAR Trimmed =
    FILTER (
        Running,
        [RunningPZ] <= PZTarget
    )
VAR TotalEUR =
    SUMX (
        Trimmed,
        SalesData[EUR]
    )
VAR TotalTrimmedPZ =
    SUMX (
        Trimmed,
        SalesData[PZ]
    )
RETURN
    DIVIDE(TotalEUR, TotalTrimmedPZ)

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.