Forum Discussion
Filter by increasing trends
Hello,
I'm new to Power Bi and could do with some help.
I have a data set with a lot of columns. But there's 2 important ones for this, Product (a list of 100+ product names in 1 column) and a submit date. The products are sliced by week or month for a count of how many lines of data exist in the period. It's a 2 part question.
1 - I would like to to create a table or matrix with a list of products and have a field which says if the trend (of count) is increasing, flat or decreasing.
2 - From that I would like to make a visual that shows only those products with an increasing trend, so a filter of the above.
Many thanks in advance
6 Replies
- mahoneypat
Microsoft Employee
You could create a measure that generates a virtual table of the date periods and your calculation, and then calculate slope of the two columns in the virtual table. You could then use an IF in your return of "increasing" for positive slope, "decreasing" for negative, etc.
You could then use this measure to filter your table visual for "increasing", for example. Unfortunately, DAX doesn't have a SLOPE function, but you can still calculation slope with a longer expression.
If you provide example data, a specific expression can be suggested.
Regards,
Pat
- Ashish_Mathur
Super User
Hi,
How do you define an increasing trend? Is an increase over just the previous month or a continuous increase over 3 consecutive months? Share sample data and show the expected result.
Thanks for the replies!
Yes I would usually look at 3 months in weekly slices to determine the trend, so 12 - 13 weeks worth of data.
The data will look like this, only with upto hundreds of thousands of rows.
ProductName SubmitDate Product 1 11/10/2020 22:39 Product 2 11/10/2020 21:10 Product 1
11/10/2020 21:37 Product 3 11/10/2020 20:20 Product 1 11/10/2020 20:01 Product 1 11/10/2020 20:00 Product 2 11/10/2020 19:05 The results I'd like to get to are - be able to build a table or matrix which looks like the below
Product 1 Increasing
Product 2 Decreasing
Product 3 Flat
Product 4 Flat
Product 5 Decreasing
And from there be able to filter other visuals by the increasing, flat or decreasing result.- Ashish_Mathur
Hi,
I would not be able to help you with such minimal information. I would typically like to see a sample dataset which shows the exact result you are expecting. If possible, show your calculations in an MS Excel file. Once i read the formulas in your MS Excel file, i should be able to help.
