Calculate average price based on certain condition

[Nun]

I have this table, 

Agent	PZ	EUR/pz
A	100	280
	50	275
C	220	260
D	130	258
E	300	255
F	500	253
G	80	252
H	20	240
I	300	239
J	700	238
K	750	235
L	50	225
M	60	220
N	40	215
O	200	210

PZ are calculated based on a formula sum (PC), EUR/pz is a formula too sum(eur)/sum(PC)
now I need to calculate the average price of highest prices falling within 10% of total PC. An example: in the table the total PC is 3500, the 10% is 350 so A,B,C are the top prices which have the pz amount within 10%, so 

we EUR 93750 (below table) PC 350 TOP EUR *PC 268 (93750/350),

PC x EUR/PC

28000

13750

52000

[v-tsaipranay]

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.

[Nun]

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. 

[v-tsaipranay]

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.

[Nun]

Hi! 
EUR/pz is a formula, it is

sum(table[EUR])/sum(tabel[pz])
Thanks!

[Nun]

for some reason these formulas the result is blank TopPricesPZ and 

TopPricesEUR the result is blank, 

[v-tsaipranay]

Hi @Nun ,

The reason you're seeing blanks for TopPricesPZ and TopPricesEUR is because your EUR/pz value is an aggregate measure and doesn't exist at row level. This prevents DAX functions like SUMX or RANKX from working properly.

To resolve this, please create a calculated column:

EURperPZ = DIVIDE('Table'[EUR], 'Table'[PZ])

Then update the logic to use this column instead of a measure. This ensures each row has a value, enabling ranking and filtering to work correctly.

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.

[Nun]

I get an average but it is not correct. I try to explain myself better. I have this table

Buyer	PZ	EUR/PZ
A	2	341.7592592592593
B	10	326.6785466785467
C	15	310.236739175187
D	11	306.7788638878643
E	8	295.0180972078594
F	10	282.64568401371145

If I take for example sale F which is 10 pieces for the selected period. But these 10 pieces are the sum of the sale of the same product several times, and that 10 is obtained with the formula sum(amount of pieces) for this product

product1	-3,4	410
product1	2,27	342
product1	4,46	322

the average that I m getting is 546,48, ToppriceEUR -617,52 ToppricesPZ -1,13(which is -3,4+2,27). In In practice, it should use not the single row of the product, but PZ and EUR/PZ, so the TOP average should take the PZ 2 and 10 whitin 10%(so if the tot PZ is 55 and 10% is 5,5, it should take PZ 2 and PZ 3,5 to reach the 5,5) and the prices 341 and 326.
I am using:

TopPricesEUR =

CALCULATE(

SUMX(table[AMOUNT_PZ]*table[ EUR/PZColumn]),

FILTER(

'table,

[CumulativePZ] <= [TenPercentVolume]

)

)

TopPricesPZ =

CALCULATE(

SUMX('Table,table[AMOUNT_PZ]),

FILTER(

'table',

[CumulativePZ] <= [TenPercentVolume]

)

)

Thanks so much!

[Ashish_Excel]

Hi,

That poasted data does not appear properly.  If possible, could you share the download link of an MS Excel file.  Put your data there and show the MS Excel formulas there used to arrive at the result.  I will convert them into measures.  If you cannot write formulas there, then via comments/textboxes, tell us what you want to do.

[Nun]

Hello. Can you please answer the request? If further information is required, please ask.

[Nun]

Considering this:
Total PZ is the sum of PZ 2838,781 (you can see from the total of table attached). 1373,59 (is a formula PZ*EUR/PZ). For the selection within 10% of the total PZ and because we want to find the top average prize, we take in consideration the PZ rows from 4,631 to 9,592 (the total is 289,578, 22nd row) because the total is >10% (283,878) from the lowest price we take only the pz that match excatly to 283,578 (this is the reason that the latest is 1027,766 = 9,592-(289,578-283,878)*264,2003=1027,766. the sum (77579,08) is the total (from 1373,59 to 1027,766) Top 10% price is 273,285 because is (77579,08/283,8761). To support there is calculated even the Deduct from last customer (5,7016 =289,578-283,876)

[v-tsaipranay]

Hi @Nun ,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  

I am also including .pbix file for your better understanding, please have a look into it:

Hope this will help you 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.

Thank you for using Microsoft Community Forum.

[Nun]

Please, could you check the latest reply? 
Thanks!

[Nun]

Hi, 

thanks so much for your support, please find enclosed ,Time ID is connected with a Calendar table to define the quarter. As well Customer ID and Product ID are connected with dimension tables to define the name of the customer and product. Now in the same quarter the same product can be sold to the same customer with different amounts and prices.  EUR/PZ do not exsist and so PZ as total for each customer. I created a column EUR/PZ but for some reason I get values different than if I create a measure. Here an example (PZ is a measuere (sum of PZ, Invoiced €/PZ column is a column created dividing EUR by PZ, the other is a measure.

INVO_TIME_ID	Customer	Delivery Customer	PRO_ID	EUR	PZ
7405	12467	12587	17449	10741,06	50,085
7442	12467	12587	16040	4706,22	22,125
9365	12467	12587	15735	5286,04	24,855
9365	12467	12587	6480	5392,16	25,354
7558	12467	12587	16040	3954,96	19,118
7323	12467	12587	16087	7805,77	38,241
7323	12467	12587	17449	2178,16	10,71
7463	12467	12587	6480	-9506,86	-48,213
7442	12467	12587	16087	4938,82	25,107
7482	12467	12587	17386	9676,88	50,125
7558	12467	12587	13402	6057,46	32,944
9558	12467	12587	6480	8808,57	48,291
9578	12467	12587	6480	8296,09	46,019

[v-tsaipranay]

Hi @Nun ,

Apologies for the delay response.

Calculated column and measure will not give same results. This is because Calculated column computes at row-level in the table whereas a measure is aggregated over a table.

when you use calculated column in visuals (like a table grouped by Customer), Power BI will SUM or AVERAGE those individual values.

The correct way to calculate average in this case is using a measure.

If this doesn’t resolve after checking it, please provide some sample data and expected output so that I will help you to resolve your issue more accurate.

Thank you.

[Nun]

Hello. Can you please answer the request? If further information is required, please ask. 

[Nun]

Thanks for supporting.
Please find enclosed a table, considering that EUR/PZ is a measure (Price Eur divided by amt PZ both are columns) and PZ is a measure too (sum of amt PZ). the end result should be Top 10% price. How: once defined the total PZ of the period, calculate the 10 of total PZ. Define the top Top 10% chosen customers total PZr are in this 283,76, in this case is 289,578, to match from the lowest PZ we remove the PZ to match excatly with 283,87 The first column is PZ*EURPZ of the selected top 10% customers. All is based on the selection of a specific product. So we have a selection of products, once selected a product and period,- we see the table attached with Cust, Del cust, PZ, EUR/PZ

Invcust	Del cust	PZ	EUR/PZ
A	AA	4,631	296,6076
A	BB	4,104	291,4206
A	CC	23,39	286,9393
A	DD	4,961	282,9954
A	EE	5,623	282,44
A	FF	10,254	278,0856
A	GG	18,298	277,7457
A	LL	15,877	277,1903
A	MM	5,623	276,6548
A	…	15,388	276,4121
A	…	6,899	276,2096
A	…	10,915	274,5112
A	…	14,16	273,9414
A	…	11,867	271,0879
A	…	9,922	268,8218
A	…	17,522	268,3449
B	…	26,46	266,9781
B	…	40,684	266,7958
A	…	5,292	266,7611
A	…	9,592	264,8415
A	…	18,524	264,3463
A	…	9,592	264,2004
A	…	21,5	263,9921
D	…	172,329	263,9917
A	…	21,791	262,395
A	…	9,262	259,0672
A	…	5,623	257,5867
A	…	74,689	257,26
A	…	9,922	255,133
E	…	42,338	255
A	…	491,009	254,875
F	…	16,207	252,721
…	…	19,912	252,5271
…	…	228,743	249,5453
…	…	50,548	248,8441
…	…	90,31	245,6905
…	…	134,544	245,2783
…	…	9,016	243,5337
…	…	259,57	242,9669
…	…	16,207	241,8905
…	…	18,705	240,7399
…	…	127,008	238,1796
…	…	37,008	237,6513
…	…	58,579	237,228
…	…	43,332	236,7659
…	…	54,874	233,3672
…	…	5,954	232,825
…	…	41,636	232,3312
…	…	40,683	232,3184
…	…	4,631	230,9696
…	…	23,816	230,571
…	…	43,324	229,2517
…	…	37,231	227,5069
…	XX	55,441	226,3702
…	YYY	4,874	224,3906
…	RRRR	29,108	222,936
…	NNNN	93,878	222,7131
…	MMMMM	9,592	220,638
…	HHHHHH	5,954	215,6416
…	LLLLLLLLL	85,931	214,1419
…	ZZZZZZZZZ	54,104	173,5557
Total		2838,761