Forum Discussion
Resolver ICalculate average price based on certain condition
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.
Resolver IHi,
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 |
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
Hello. Can you please answer the request? If further information is required, please ask.