Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
m4xon
Helper II
Helper II

Add trend column to the table

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,

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @m4xon ,

According to your description, here's my solution.

1.Add an index column in Power Query.

vyanjiangmsft_0-1685527014310.png

2.Create a measure.

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

Get the correct result:

vyanjiangmsft_1-1685527109356.png

vyanjiangmsft_2-1685527122880.png

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.

 

 

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @m4xon ,

According to your description, here's my solution.

1.Add an index column in Power Query.

vyanjiangmsft_0-1685527014310.png

2.Create a measure.

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

Get the correct result:

vyanjiangmsft_1-1685527109356.png

vyanjiangmsft_2-1685527122880.png

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
Helper II
Helper II

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
Super User
Super User

Read about the INDEX  function.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.