Forum Discussion
Calculate average price based on certain condition
Hi Nun ,
Thank you for reaching out to the Microsoft Fabric Community Forum.
As you've described, you're aiming to compute the average unit price (EUR/pz) of the highest-priced entries, constrained to the top 10% of total PZ volume. The idea is to rank entries by EUR/pz in descending order and include rows until their cumulative PZ reaches the 10% threshold then calculate a weighted average on that subset.
The approach provided by bhanu_gautam captures this logic accurately using a combination of ranking, cumulative sums, and conditional filters in DAX.
As a small note, please ensure that missing or blank values (such as null PZ or EUR/pz) are excluded in your model to avoid calculation errors.
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.
Hello, I had to create a table because I got the error of a circula dependency was detected. After that when I use the average formula, I get an error fetching data: there's not enough memory to complete this operation.
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.