Occurence number or rank for calculated column

[john-paul]

Hi,

Hoping someone can help me add a calculated column that ranks or counts occurrence number of a Product Id within a Region based on the order number.

Where there are multiple instances of the Product Id sold within a Region, then the earliest Order is ranked or counted 1st.

Thanks.

Region	Product Id	Order	Sales	Expected Value
North	901	100001	100	1
North	901	100002	100	2
North	902	100003	120	1
North	902	100004	120	2
North	902	100005	120	3
West	901	100006	100	1
West	902	100007	120	1
West	902	100008	120	2
West	903	100009	130	1
West	903	100010	130	2
East	901	100011	100	1
East	901	100012	100	2
East	901	100013	100	3
East	902	100014	120	1
East	903	100015	130	1
East	903	100016	130	2
South	901	100017	100	1
South	901	100018	100	2
South	901	100019	100	3
South	901	100020	100	4
South	901	100021	100	5
South	903	100022	130	1

[Anonymous · Accepted solution]

Create new column as 

column = CALCULATE(count(Table1[Order]);FILTER(Table1;Table1[Region]=EARLIER(Table1[Region])&&Table1[Product Id]=EARLIER(Table1[Product Id])&&Table1[Order]<=EARLIER(Table1[Order])))

I hope it works for you

[john-paul]

Thank you alsasloren.

That is awesome it worked perfectly!

Can the formula also be modified as a measure?

[Anonymous]

Hi @john-paul 
Please check the below code.

Measure = CALCULATE(

COUNT(Table1[Order]);

FILTER(ALL(Table1);

SUMX(FILTER(Table1;EARLIER(Table1[Region])=Table1[Region]);Table1[Column])&&

SUMX(FILTER(Table1;EARLIER(Table1[Product Id])=Table1[Product Id]);Table1[Column])&&

SUMX(FILTER(Table1;EARLIER(Table1[order])<=Table1[Order]);Table1[Column])

)

)

[john-paul]

Thanks again alsasloren for the quick response.

I wasn't able to get this one to work. I changed each of the Table1[Column] references to Region, Poduct Id and Order.

Do I need to have these inside another function?

[Anonymous]

Hi again I forgot to declare column content.

Actually it wasn't so important but i have to create it. just create new column and give a value as 1

Like;
column = 1