cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Per-J Helper I
Helper I

Adding index number based on category

Hello, folks!

How can I add an index number that runs based on category type stated in another column? See the example picture. This is what I want to achiecve. I'd like the index column to give a number-series to all categories seperatly. Essentialy I would then be able to sort on category and get a continous series running from 1 to "whatever-number".

Aditionally I would like the index series in each category to run according to date in another column.

 

DateCategoryIndex
15.03.2018Child1
12.04.2018Adult2
13.04.2018Adult3
02.04.2018Adult1
25.06.2018Child2
22.05.2018Adult4
27.09.2018Child4
22.08.2018Child3
15.10.2018Child5
02.10.2018Adult5



Is there any way to do this? I found this this forum-post interesting, but I can't really get it to fit my goal exactly. However, maybe some of you understand how to use this info for my purpose.

 

Best:

- Per-J.H.

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Solution Sage
Solution Sage

Re: Adding index number based on category

What if you add another column called FkDte with this formula

 

=Table1[Date]+RANDBETWEEN(1,1000) / 10000

 

and then add another columns with this formula

 

=CALCULATE(
COUNTROWS( Table1 ),
ALLEXCEPT( Table1, Table1[Category] ),
Table1[FkDte] < EARLIER( Table1[FkDte] )
) + 1

 

 

by the way, do you only have 2 columns in the original table (Date, Category), or do you have other columns that could avoid the usage of the FkDte column?

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

10 REPLIES 10
LivioLanzo Solution Sage
Solution Sage

Re: Adding index number based on category

@Per-J

 

You need to convert your date column to real dates and do it like this:

 

 

 

=
CALCULATE (
    COUNTROWS ( Table1 ),
    ALLEXCEPT ( Table1, Table1[Category] ),
    Table1[Date] < EARLIER ( Table1[Date] )
)
    + 1

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User I
Super User I

Re: Adding index number based on category

@Per-J Please try this using "New Column" 

 

Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC,Dense)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Per-J Helper I
Helper I

Re: Adding index number based on category

@PattemManohar, @LivioLanzo

 

Both of the solutions mentioned here gave close to what I'm looking for. Almost there. I really liked the simplicity of this, last example.

There is one major problem, though. Whenever there is similar dates, this code returns the same index-value. I must have unique values for each row, even if there are the same dates. Its ok that observations on the same date have 4, 5 and 6 as values, as long as they are before the index number for a later date.

My new column gave me four #11 - values, as there are four observations on the same day. These values have to be 11, 12, 13 and 14.

Is there a neat way of getting this done?

Highlighted
LivioLanzo Solution Sage
Solution Sage

Re: Adding index number based on category

@Per-J

 

could you post this dataset where you get dupes?

 

thanks

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User I
Super User I

Re: Adding index number based on category

@Per-J Ok, then just remove the DENSE from the Rank. So it will be...

 

Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC)


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Per-J Helper I
Helper I

Re: Adding index number based on category

@LivioLanzo

The example I've given is only tentative and the real dataset is approx. 10k rows with a lot of data and columns. It's classified info, so I'd rather give examples. Under I've posted my results using your formulas. As you can see, same dates gives same number in both cases. In stead of 3, 3, 3 and 7, 7, 7, I need, 3, 4, 5 and 7, 8, 9.

Hope this helps understanding the problem.

 

Both.PNGChildren.PNG

 

Adults.PNG

 

 

Per-J Helper I
Helper I

Re: Adding index number based on category

@PattemManohar

 

Removing the "dense"-statement only yealds the same result as LivioLanzo. There is still identical index-number on the same dates.

Tricky. Smiley Frustrated

Per-J Helper I
Helper I

Re: Adding index number based on category

Dear @PattemManohar @LivioLanzo
Thanks for helping with this issue. You are most helpful.

I came to think that maybe there is a better way of getting this job done. If there is a formula, measure (or some other function), that can do two operations in separat orders.

 

First: sort by date

Then: give assending index numbers based on (only) category

 

This way, date is not in the mix when doing the numbering task. Is this a way to go, or even possible?


Best:

- Per-J. H.  

LivioLanzo Solution Sage
Solution Sage

Re: Adding index number based on category

What if you add another column called FkDte with this formula

 

=Table1[Date]+RANDBETWEEN(1,1000) / 10000

 

and then add another columns with this formula

 

=CALCULATE(
COUNTROWS( Table1 ),
ALLEXCEPT( Table1, Table1[Category] ),
Table1[FkDte] < EARLIER( Table1[FkDte] )
) + 1

 

 

by the way, do you only have 2 columns in the original table (Date, Category), or do you have other columns that could avoid the usage of the FkDte column?

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors