Issue trying to calculate gradient using Power BI

Question

I am trying to calculate the gradient of the trendline passing through a series of points contained within my dataset. I have researched to see if there are built in functions to do this and there doesn't seem to be, so I am doing it manually. I'm not a DAX expert (nor probably a maths expert either!).

I have created a table in excel to walk through a simple example so I know what I'm aiming for:

In the Power BI environment, there are two tables joined on the "Month&Year" columns. An abridged illustration of these tables is below:

Please note the "Orders" measure from the illustration is referred to as "Special orders per day" in the Power BI code.

Step 1

Create the measure that averages the month numbers:

Average of months =
    - AVERAGEX (
        SUMMARIZE (
            CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
            Query_GSR[Month&Year],
            "AvMonths", AVERAGE ( Query_GSR[MonthNumForSlope] )
        ),
        [AvMonths]
    )

I use AVERAGE in the expression part so that the record for Sept-2018 has a 21 in the "AvMonths" column and then for Oct-2018 it says 22. I guess I could have used MIN or MAX because they will all say 21 or 22 depending on the month (only one to avoid would be SUM as this would add them all up).

I also tried to do this by summarizing and then creating a NATURALLEFTOUTERJOIN to the User_Friendly_Months table to get the month number for these months and when incorporating that into the rest of this procedure the measure took forever to calculate (even though it actually worked in the end somehow).

Step 2

Do the same for orders:

Average of special orders =
- AVERAGEX (
    SUMMARIZE (
        CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
        Query_GSR[Month&Year],
        "Special OPD", [Special orders per day]
    ),
    [Special OPD]
)

Step 3

Perform the calculation that goes through to step "C" in my original picture:

Column_C_Step =
SUMX (
    SUMMARIZE (
        CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
        Query_GSR[Month&Year],
        "Special OPD", [Special orders per day],
        "MonthNum", AVERAGE ( Query_GSR[MonthNumForSlope] )
    ),
    ( [Special OPD] + [Average special orders] )
        * ( [MonthNum] + [Average of MonthNums] )
)

Instead of returning -11.95 in my example, the measure returns zero.

When I do this:

Check_orders_worked =
SUMX (
    SUMMARIZE (
        CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
        Query_GSR[Month&Year],
        "Special OPD", [Special orders per day],
        "MonthNum", AVERAGE ( Query_GSR[MonthNumForSlope] )
    ),
    [Special OPD]
)

...I get 1188.9, which is the total of "Orders" in my Excel table illustration (so must be working).

When I do this:

Check_months_worked =
SUMX (
    SUMMARIZE (
        CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
        Query_GSR[Month&Year],
        "Special OPD", [Special orders per day],
        "MonthNum", AVERAGE ( Query_GSR[MonthNumForSlope] )
    ),
    [MonthNum]
)

...I get 43, which is the total of Month_Num in my illustration (so again, must be working).

But when I attempt to perform the equivalent of a SUMPRODUCT on A and B to get C, it returns zero.

Can anyone shed any light on what on earth is going on??

It is driving me insane.

Or if there is a simpler way to do a gradient calculation I will cry with joy.

Thank you

UPDATE

For completeness here is the measure that worked:

Step_C_Measure =
VAR _OrdersAverage = [Average special orders]
VAR _MonthsAverage = [Average of MonthNums]
RETURN
    SUMX (
        SUMMARIZE (
            CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
            Query_GSR[Month&Year],
            "Special OPD", [Special orders per day],
            "MonthNum", AVERAGE ( Query_GSR[MonthNumForSlope] )
        ),
        ( [Special OPD] + _OrdersAverage )
            * ( [MonthNum] + _MonthsAverage )
    )

Then Step D:

Step_D_Measure =
VAR _MonthsAverage = [Average of MonthNums]
RETURN
    SUMX (
        SUMMARIZE (
            CALCULATETABLE ( Query_GSR, ALLSELECTED ( User_Friendly_Months ) ),
            Query_GSR[Month&Year],
            "Special OPD", [Special orders per day],
            "MonthNum", AVERAGE ( Query_GSR[MonthNumForSlope] )
        ),
        ( [MonthNum] + _MonthsAverage )
            * ( [MonthNum] + _MonthsAverage )
    )

And finally to get the gradient:

Special order gradient =
DIVIDE ( Step_C_Measure, Step_D_Measure, "" )

This seems like way too much work to me. `-23.9` is simply `(582.5 - 606.4)/1`, the change over one month. Are you just using two points, or are you trying to create a more general linear regression? — Alexis Olson, Nov 14 '18 at 18:48
@AlexisOlson I’ve just included two data points for illustration and so I could easily re-perform on the calculator to check. The aim is to use this for a gradient calculation over perhaps 3,6,9 and 12 months, by customer (there are hundreds of these potentially) so that I can create a report that ranks all customers from smallest (biggest negative gradient) to largest, flagging which customers are declining the fastest over each time period. — mickeyt, Nov 14 '18 at 18:53
Can you edit your post and run your DAX through [DAX Formatter](https://www.daxformatter.com/) so that it's more readable? It's a real pain to have to scroll left and right. (Note: It expects a `MeasureName =` to precede the formula.) — Alexis Olson, Nov 14 '18 at 20:14
Apologies - had wondered how to do that. Please see above. My approach also works for multiple months but is linked to a slicer that is controlling the User_Friendly_Months table. When I had all 12 months showing it was just a pain to add all up and check what I was seeing was what it should be, so I dropped the slicer to September and October just so I could check it. You'll see this is controlled in the CALCULATETABLE function where I say ALLSELECTED(User_Friendly_Months). — mickeyt, Nov 14 '18 at 20:42

score 0 · Accepted Answer · answered Nov 14 '18 at 19:09

0

In a question about multiple linear regression, I linked to a community post that covers basic linear regression.

In your case, the formula for the slope can be calculated similar to this:

Slope = 
VAR RowCount = COUNTROWS(Query_GSR)
VAR Sum_X = SUMX(Query_GSR, Query_GSR[Month_Num])
VAR Sum_Y = SUMX(Query_GSR, Query_GSR[Orders])
VAR Sum_XY = SUMX(Query_GSR, Query_GSR[Month_Num] * Query_GSR[Orders])
VAR Sum_XX = SUMX(Query_GSR, Query_GSR[Month_Num] * Query_GSR[Month_Num])
RETURN DIVIDE(RowCount * Sum_XY - Sum_X * Sum_Y, RowCount * Sum_XX - Sum_X * Sum_X)

This works for a regression on multiple months, not just two.

answered Nov 14 '18 at 19:09

Alexis Olson

38,724
7
42
64

Brilliant I will try that. It will still bug me why what I tried didn’t work from an understanding of DAX point of view but this will let me try out the report and see if it actually helps at all. Thank you very much. – mickeyt Nov 14 '18 at 19:13
1

@mickeyt I think the problem with your code is that in your Column_C_Step measure, the measures [Average special orders] and [Average of MonthNums] are not being evaluated in the context you want. If you declare them as variables (e.g. VAR AvgOrders = [Average of special orders]) and pass those variables into the SUMX, I think you'll get the -11.95 you expect – Alexis Olson Nov 14 '18 at 22:16
I’ll make that change in the morning and let you know - thank you so much for having a look. – mickeyt Nov 14 '18 at 22:31
Hi again. Works like a dream! Did something similar for "step D" assigning the average to a variable and then performing the row value minus the average and squaring it. Final measure divides D into C to get the gradient. Very interesting seeing that as the basis for a customer ranking - rather than eyeballing a load of line charts this draws attention with a single "score" so think it'll be really useful. Thank you very much for your help! – mickeyt Nov 15 '18 at 18:26

Issue trying to calculate gradient using Power BI

1 Answers1