Forum Discussion

mediumlevel2024's avatar
mediumlevel2024
Frequent Visitor
2 years ago
Solved

Price Average per selected TopN

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 b...
"TOPN"
  • Rupak_bi's avatar
    Rupak_bi
    2 years ago

    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's avatar
mediumlevel2024
Frequent Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.