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]

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