Forum Discussion
How to calculate dynamic index
Hi,
I have sales of a products at different time points. Now I want to plot the sales in power bi desktop based on index instead of Time as mentioned in the below table:
Calculating index dynamically for diffeent products is a challenge for me. Need your advise on how to achieve this.
| Product | Time | Index |
| Product1 | 201801 | 0 |
| Product1 | 201802 | 1 |
| Product1 | 201803 | 2 |
| Product1 | 201804 | 3 |
| Product2 | 201803 | 0 |
| Product2 | 201804 | 1 |
| Product2 | 201805 | 2 |
| Product2 | 201806 | 3 |
| Product2 | 201807 | 4 |
Try this code to build a calculated column in your table.
dynamic Index each product = RANKX(FILTER('Table','Table'[Product]=EARLIER('Table'[Product])),'Table'[Time],,ASC,Dense)-1Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
adinarayana123 , where is a product in the sample for "index dynamically for different products"
Please share a better sample if possible
I think you need to use rank
For Rank Refer these links - Check for sub Category rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns Try this code to build a calculated column in your table.
dynamic Index each product = RANKX(FILTER('Table','Table'[Product]=EARLIER('Table'[Product])),'Table'[Time],,ASC,Dense)-1Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
