Add trend column to the table

[m4xon]

Hi Guys,

I need your help with following problem.

I have a table that looks like this:

YQ

2023-2

2023-3

2023-4

2024-1

2024-2

2024-3

Based on context filter other column in report view there will be always only 5 rows, so sth like that:

2023-3

2023-4

2024-1

2024-2

2024-3

or 

2023-2

2023-3

2023-4

2024-1

2024-2

The problem is that I need to show on visual trend line from 100 - 20, so I need a measure that will allows me to create following table:

2023-2	100
2023-3	80
2023-4	60
2024-1	40
2024-2	20

or 

2023-3	100
2023-4	80
2024-1	60
2024-2	40
2024-3	20

depend of user selection.

Thank you in advance,

[v-yanjiang-msft · Accepted solution]

Hi @m4xon ,

According to your description, here's my solution.

1.Add an index column in Power Query.

2.Create a measure.

Measure =
100 - ( MAX ( [Index] ) - MINX ( ALLSELECTED ( 'Table' ), 'Table'[Index] ) ) * 20

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[m4xon]

I tried with INDEX, but don't know how to use it properly. Most examples I found was related to the metrics values, so it was a part of CALCULATE, I have only text values 2023-2, 2023-3 etc. 

[lbendlin]

Read about the INDEX  function.