topic Re: Price Average per selected TopN in DAX Commands and Tips

Price Average per selected TopN

mediumlevel2024 — Fri, 30 Aug 2024 06:50:01 GMT

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

Re: Price Average per selected TopN

Rupak_bi — Fri, 30 Aug 2024 07:21:32 GMT

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

)

Re: Price Average per selected TopN

ThxAlot — Fri, 30 Aug 2024 08:17:19 GMT

Re: Price Average per selected TopN

mediumlevel2024 — Fri, 30 Aug 2024 11:13:36 GMT

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?

Re: Price Average per selected TopN

Anonymous — Mon, 02 Sep 2024 02:18:54 GMT

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.

Re: Price Average per selected TopN

mediumlevel2024 — Mon, 02 Sep 2024 07:22:12 GMT

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

Re: Price Average per selected TopN

Rupak_bi — Mon, 02 Sep 2024 09:53:38 GMT

you are welcome