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 be used.
Example values look as follows:

Article IDArticle ID CompetitorPrice
123341,50
123351,56
123361,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

"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
        )
    )

6 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    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

      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

    • 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.