Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm trying to get the slope from LINESTX for each product so that I can find the top N products by their slope.
I have a simple PBIX with test data that I'm allowed to share but I don't know how to attach it to this post...so I have some screenshots below.
I have a simple model, consisting of a Date, Sales, and Products tables. The Date table is related to the Sales table by Date and the Sales table is related to the Products table by SKU.
Below are my measures. The "Slope" measure is the same as "Qty LR" except I'm returning slope instead of y.
Total Qty =
CALCULATE(
SUM('Sales'[Total Units])
)
Qty LR =
VAR line =
LINESTX(
ALLSELECTED('Sales'),
[Total Qty],
'Sales'[Year]
)
VAR slope = SELECTCOLUMNS( line, [Slope1])
VAR intercept = SELECTCOLUMNS( line, [Intercept])
VAR x = SELECTEDVALUE( 'Date'[Year Number] )
VAR y = x * slope + intercept
RETURN y
Slope =
VAR line =
LINESTX(
ALLSELECTED('Sales'),
[Total Qty],
'Sales'[Year]
)
VAR slope = SELECTCOLUMNS( line, [Slope1])
VAR intercept = SELECTCOLUMNS( line, [Intercept])
VAR x = SELECTEDVALUE( 'Date'[Year Number] )
VAR y = x * slope + intercept
RETURN slope
The image below shows the slope of -6.31 which is for all the sales data. I want the slope to change for each product.
If I click on a product from the matrix then the table is filtered specifically for that product and the slope is generated for that product only.
For example, the slope for Product A is 6.40
For example, the slope for Product B is -21.60
How can I modify the Qty LR and Slope measures to return the slope for each product without having to use another visual to filter for a specific item?
The end result should be a table like in the first screenshot with the red box but instead of the slope being the same for every product, I want the slope to change for each product.
My real data set has thousands of products and I want to use the slope to find the top N or bottom N products.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |