Forum Discussion

Nun's avatar
Nun
Icon for Resolver I rankResolver I
1 year ago

Calculate average price based on certain condition

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 ...
Need Help
Nun's avatar
Nun
Icon for Resolver I rankResolver I
1 year ago

I get an average but it is not correct. I try to explain myself better. I have this table

BuyerPZEUR/PZ
A2341.7592592592593
B10326.6785466785467
C15310.236739175187
D11306.7788638878643
E8295.0180972078594
F10282.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,4410
product12,27342
product14,46322

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's avatar
Ashish_Excel
Icon for Solution Supplier rankSolution Supplier
1 year ago

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's avatar
    Nun
    Icon for Resolver I rankResolver I
    1 year ago

    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's avatar
    Nun
    Icon for Resolver I rankResolver I
    1 year ago

    Hello. Can you please answer the request? If further information is required, please ask.