Forum Discussion
Resolver ICalculate average price based on certain condition
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.
Resolver II 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:
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.
Ashish_ExcelSolution Supplier
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
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 - Nun
Please, could you check the latest reply?
Thanks! 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
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/PZInvcust
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
- 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) - Nun
Hello. Can you please answer the request? If further information is required, please ask.
- Nun
Hello. Can you please answer the request? If further information is required, please ask.