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 @v-tsaipranay , thanks so much for supporting, I really appreciate it!
I created the Ranked Table, then I tryed to create the table (Top10Pcttbale)  to calculate Cumulative PZ and Filter for Top 10%, but I can't because I get this error:"The first argument of EARLIER/EARLIEST is not a valid column reference in the earlier row context.". Then I created the measure to calculate Weighted Avg Price for Top 10% PZ. But I do not understand the relationship between the three functions. My final aim is to determine the average price of the prices that have a quantity of 10% of the total, for a given period, for a selected product. In the attached tables, the top price 273.28 is the average price of product X for the first quarter, for the total of products that are equal to 10% of the overall total

[Nun]

Hi, yes you are right, my apologies. But that 

Top10PctTable

 is not taking in consideration the "remaining PZ", right?

[v-tsaipranay]

Hi @Nun ,

Yes, it won’t include the remaining PZ because we’re explicitly specifying the top 10 PZ, so those others won’t be considered.

Thank you.

[v-tsaipranay]

Hi @Nun ,

To calculate the weighted average EUR/pz for the top 10% of total PZ (prioritizing by highest price). I implemented a DAX-based approach and verified the result.

For your reference, I’ve attached the .pbix file so you can review the implementation and DAX measures used.

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