Price Average per selected TopN

[mediumlevel2024]

Hello everyone,
I currently have the problem that I need the average price per selected TopN. This results in price duplication and the average does not recognize that the individual values should be used.
Example values look as follows:

Article ID	Article ID Competitor	Price
123	34	1,50
123	35	1,56
123	36	1,56

ØN price =

VAR SelectedN = SELECTEDVALUE('Average N'[Average N])

RETURN

CALCULATE(

    AVERAGE(table[price]),

    TOPN(

        SelectedN,

        table,

        table[price],

        ASC

    )

)

If I enter TopN = 1, I get the expected value 1.50. But if I enter TopN = 2, I get the value 1.54 instead of 1.53. This happens because the value 1.56 occurs twice, but the calculation should differentiate according to TopN.

Many thanks in advance

[ThxAlot]

[mediumlevel2024]

Hi thanks for your reply, but it still not working. I do have Prices (sorted) like 9,50€, 9,50€, 10,0€, 10,0€ and 11,30€ and if I chose N = 1 it gives me 9,50€ as average. If I chose N = 2 I would expect an average value of 9,50€ but i receive a value of 9,75€ because this measure skips the second 9,50€.
Any further ideas?

[Anonymous]

Hi @mediumlevel2024 ,

You can consider increasing the value by a very small multiple at the same time to get the ordering, I did simple samples and you can check the results as below:

Column = RANKX('Table',[Value]*[ID]*0.0000000001,,ASC,Dense)

Measure = var _s = SELECTEDVALUE('Table 2'[Column])
RETURN CALCULATE(AVERAGE('Table'[Value]),TOPN(_s,'Table',[Value],ASC))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi · Accepted solution]

Hi,

Run the TopN function on some unique column.

ØN price =

VAR SelectedN = SELECTEDVALUE('Average N'[Average N])

RETURN

CALCULATE(

    AVERAGE(table[price]),

    TOPN(

        SelectedN,

        table,

        table[Article ID],

        ASC

    )

)

[mediumlevel2024]

Hello, this tip was very helpful. I grouped the prices according to the article number and then inserted an index. The index is now used in my measure for clear assignment.
Many thanks

[Rupak_bi]

you are welcome