Forum Discussion
Dynamic Index with Split Data Set
Hi,
I have a dataset that looks like:
| ID | Sales | Period | Index | % of Total |
| 123 | $10 | Current Year | 1 | 50% |
| 223 | $5 | Current Year | 2 | 100% |
| 122 | $17 | Prior Year | 1 | 33% |
| 222 | $12 | Prior Year | 2 | 66% |
| 322 | $3 | Prior Year | 3 | 100% |
Which I use to create a scatter plot to show current vs prior year sales. In PowerBi, the issue comes in when I need to filter since the index and the % of index (which is just index / max index per period) are both manually done in Excel. So this issue is two fold as I need to create a dynamic index, but also need an index for each Period.
For reference, here is the exhibit I use in Excel:
Thanks!!
2 Replies
wacook - here's the DAX for a dynamic index based on the first 3 columns of your table. If you dont need columns in your visual, you can remove them from the ALLSELECTED().
It is made dynamic because of the PARTITIONBY, which resets the RANK every change of period.
Index = RANK(DENSE, ALLSELECTED( 'Table (3)'[ID], 'Table (3)'[Period], 'Table (3)'[Sales] ) , ORDERBY( 'Table (3)'[Sales],DESC), LAST, PARTITIONBY( 'Table (3)'[Period] ))If this works for you, please mark it as the solution.
wacook - Did this work? Please could you mark it as the solution if yes, this helps other users find it.
