Calculate average price based on certain condition

Hi Nun ,

You want to find the average unit price (EUR/pz) for the top 10% of your total volume based on price. The method you used is correct but caused errors because you created calculated columns that depend on each other, which Power BI doesn’t allow.

The memory error happens because processing large data inefficiently takes too much space. To fix this, use DAX measures instead of calculated columns, simplify your data, and write formulas that don’t refer to themselves.

To avoid these issues and improve performance, we suggest the following:

• Implement logic using Measures instead of Calculated Columns/Tables, measures are evaluated at query time and are more memory-efficient, avoiding circular references.
• Ensure rows with missing values for PZ or EUR/pz are excluded to prevent unexpected behavior:

FILTER('YourTable', NOT(ISBLANK('YourTable'[PZ])) && NOT(ISBLANK('YourTable'[EUR/pz])))

• Use Variables for Intermediate Steps, this helps streamline execution and improve clarity:

AveragePrice :=
VAR TotalPZ = SUM('YourTable'[PZ])
VAR Threshold = TotalPZ * 0.1
VAR RankedTable =
    ADDCOLUMNS(
        FILTER(ALL('YourTable'), NOT(ISBLANK('YourTable'[PZ])) && NOT(ISBLANK('YourTable'[EUR/pz]))),
        "Rank", RANKX(ALL('YourTable'), 'YourTable'[EUR/pz], , DESC, DENSE)
    )
VAR CumulativeTable =
    ADDCOLUMNS(
        RankedTable,
        "CumulativePZ",
        CALCULATE(
            SUM('YourTable'[PZ]),
            FILTER(RankedTable, [Rank] <= EARLIER([Rank]))
        )
    )
VAR FilteredRows =
    FILTER(CumulativeTable, [CumulativePZ] <= Threshold)
VAR TopPZ = SUMX(FilteredRows, 'YourTable'[PZ])
VAR TopEUR = SUMX(FilteredRows, 'YourTable'[PZ] * 'YourTable'[EUR/pz])
RETURN
    DIVIDE(TopEUR, TopPZ)

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.