Calculate number of competitors cheaper than me

[rasmusnielsen]

Hey guys,

I am trying to count the number of competitors which are cheaper than me. Currently I just have a table with a YearMonth dimension and the following measure:

Count({<[Competitor ID] = {"=avg([My Discount] - [Competitor Discount]) < 0"}>} distinct [Competitor ID] )

I got the measure from this post:

count sum in set analysis

My problem is that it seems like the resulting table is disregarding the YearMonth dimension. It is only showing the correct number when I select a specific YearMonth (such as 2018-jan).

Any suggestions?

[rasmusnielsen]

sum(if(avg(total <CompetitorID> [My Discount] - [Competitor Discount]) < 0,1))

I managed to this test this expression, and it also works as you described. Thanks for the help.

[ogautier62]

ok thank's Rasmus

I was thinking at this case :

ID      date            diff

C1     01-apr             -0.2

C1     02-apr             +0.1

C2     01-apr             -0.2

C2     02-apr             +0.3

C1 ok avg diff<0

C2 not OK because one diff<0 but avg >0

good dev