Forum Discussion

Gondi63's avatar
Gondi63
Helper I
3 years ago

Bottom 5 Trends (Slope, Linear Regression)

I have a group of locations with values over time. I'd like to highlight the locations that are trending the worst, not necessarily the locations which have the worst actual numbers. For instance, go...
Need Help
TomMartens's avatar
TomMartens
Super User
3 years ago

Hey Gondi63 ,

 

here you will find a pbix: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ETrmBE_1L9FLipANeN9z33EBM6wr1hzQA264NKfum91XBw?e=HWNaH0

 

I did not modify your data model, I only added two new measures:

  • Measure Check
  • Measure Integrated

You will find two charts, see the image below:

Two measures are doing the same but different.

 

Both measures determine the locations to be considered using SELECTCOLUMNS( TOPN( ... )).

TOPN returns the number of Rows to be considered, which is dynamic using the number slicers. SELECTCOLUMNS is removing all unnecessary columns, and a single-columned table remains. I call this table the proxy table.

Two complex parts must be tackled: a generic one and a specific one.
The specific one is determining the slope per location, this has become simpler with the introduction of the new DAX functions LINESTX and LINEST. The specific part is the computation of a numeric expression. This is simple because the numeric expression will be computed in the outer row context. This outer row context can be modified by using calculate.
The outer row context must contain all Locations, but the numeric expression must be computed separately for each location. Creating the table used for the iteration is the generic part that can be more complex than computing the numeric expression. The outer context is responsible for creating a proxy that is used to check if a location must be considered or not. I use the following to create the iterator for the outer context

CALCULATETABLE(
    VALUES( 'Location'[Location] )
    , ALL( 'Location' )
)

Could you check if the result of the measures is still returning the expected result when you use Market as an additional filter?

I use ADDCOLUMNS (outer context , "slope" , <numeric expression> ) to add a numeric expression to a row, the location.

The integrated measure contains a final step that uses the "original" numeric expression when the current location is part of the proxy table. This approach requires the integration of the computation of the proxy table to each existing measure or the creation of explicit measures. Still, it performs better (of course this depends of the number of rows) in comparison with using the check measure approach and the visual level filter:

Hopefully, this provides what you are looking for.

 

Regards,
Tom