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

[Nun]

Hi, 

thanks so much for all the support, I tested the file attached, but the result is not what it should be. Please find enclosed how should be calculated the avg based on the top price with a PZ amount excatly macthing with the 10% of total PZ.

Considering that the 10% is 350, I will take all the agents with till the total is equal to the 10% (A-B-C). now the total doesn't match because it is 370, so from the last will be used 200 (370-350 is 20, so from C=220, to match to 350 we reduct it by 20). Then we need to moltiply the PZ for the EUR/pz, but for agent C will be 200*8,08. The total will be diveded by the 10%(350). The result is 8.767. In the power BI file the Top10Pct_Avg_Price is 9.29. Hope I was clear. Thanks!

[v-tsaipranay]

Hi @Nun ,

I’ve reviewed and updated the file. It appears the difference in results was due to a mismatch between your dataset and the one I initially referenced.

The calculations and measures you used were correct—no issues there. The logic was sound; only the data source differed.

I am also including the screenshot for your reference:

Please feel free to open the file and explore the visuals and calculations.

Thank you.

[v-tsaipranay]

Hi @Nun ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Nun]

Hi, thanks for supporting.

I am testing it, because our scenario is a little different. First of all Eur/PZ is a measure, we have Eur for each row. It means we have the same agent that can buy the same product for several times in the same quarter, with different prize (Eur) and different quantity (PZ). 

[v-tsaipranay]

Hi @Nun ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Nun]

Hello,
thanks a lot! Here a table consider that PZ is sum of Pz (measure) and EUR/PZ is a measure too.I should get 

Customer	Delivery customer	PZ	EUR/pz
A	AA	4,631	296,6076441
A	BB	4,104	291,4205653
A	CC	23,39	286,9392903
A	CC	4,961	282,9953638
A	…	5,623	282,4399787
A		10,254	278,0856251
A		18,298	277,7456553
A		15,877	277,1902752
A		5,623	276,6548106
A		15,388	276,4121393
A		6,899	276,2095956
A		10,915	274,5112231
A		14,16	273,9413842
A		11,867	271,0878908
A		9,922	268,8218101
A		17,522	268,3449378
B		26,46	266,9780801
C		40,684	266,795792
A		5,292	266,7611489
A		9,592	264,8415346
A		18,524	264,3462535
A		9,592	264,2003753
A		21,5	263,992093
D		172,329	263,9917251
A		21,791	262,3950255
A		9,262	259,0671561
A		5,623	257,5866975
A		74,689	257,2599713
A		9,922	255,1330377
A		42,338	255
A		491,009	254,8750023
….		16,207	252,7210465
		19,912	252,5271193
		228,743	249,5453413
		50,548	248,844069
		90,31	245,6905105
		134,544	245,2783476
		9,016	243,5337178
		259,57	242,9669453
		16,207	241,8905411
		18,705	240,7399091
		127,008	238,1796422
		37,008	237,6513186
		58,579	237,2280169
		43,332	236,7659005
		54,874	233,3671684
		5,954	232,8249916
		41,636	232,3311557
		40,683	232,3184131
		4,631	230,969553
		23,816	230,5710447
		43,324	229,251685
		37,231	227,5069163
		55,441	226,3701953
		4,874	224,3906442
		29,108	222,9359626
		93,878	222,7130957
		9,592	220,6380317
		5,954	215,6415855
		85,931	214,1419278
		54,104	173,5557445
Total		2838,761

[bhanu_gautam]

@Nun 

Create a measure to calculate the total PC:

TotalPC = SUM('Table'[PZ])

Create a measure to calculate 10% of the total PC:

ThresholdPC = [TotalPC] * 0.1

Create a calculated column to rank the rows by EUR/pz:

Rank = RANKX('Table', 'Table'[EUR/pz], , DESC, DENSE)

Create a measure to calculate the cumulative sum of PZ:

DAX
CumulativePZ =
CALCULATE(
SUM('Table'[PZ]),
FILTER(
ALL('Table'),
'Table'[Rank] <= MAX('Table'[Rank])
)
)

Create a measure to filter the rows within the 10% threshold:

DAX
TopPricesPZ =
CALCULATE(
SUM('Table'[PZ]),
FILTER(
'Table',
[CumulativePZ] <= [ThresholdPC]
)
)

Create a measure to calculate the sum of EUR for the selected rows:

DAX
TopPricesEUR =
CALCULATE(
SUM('Table'[EUR/pz] * 'Table'[PZ]),
FILTER(
'Table',
[CumulativePZ] <= [ThresholdPC]
)
)

Finally, create a measure to calculate the average price:

DAX
AveragePrice = [TopPricesEUR] / [TopPricesPZ]

[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.