Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Thank you alsasloren.
That is awesome it worked perfectly!
Can the formula also be modified as a measure?
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |