Calculate "Down factor" for each row using DAX in Power BI

Question

I have the following format of data:

Machine | Production | Production Factor | Down Factor | Order
--------|------------|-------------------|-------------|------
M1      | 0          | 0                 | 0           | 100
M2      | 187370     | 0                 | 0           | 115
M3      | 7054       | 0                 | 0           | 130
M4      | 39428      | 1                 | 24160       | 150
M5      | 6945       | 0                 | 0           | 160
M6      | 0          | 1                 | 24160       | 181
M7      | 19720      | 1                 | 4440        | 200
M8      | 4440       | 1                 | 0           | 220
M9      | 30000      | 0                 | 0           | 230
M10     | 20000      | 0                 | 0           | 240

Machine, Production, Production Factor and Order fields are extracted from database I need to calculate "Down Factor" for every machine based on the formula in scope of ascending Order field:

Down Factor =
    SUM ( Production * Production Factor * IF(CurrentRowOrder > RowOrder THEN 1 ELSE 0 ) )

As for example M4 (CurrentRowOrder = 150):

 6945 * 0 * 1 (160 > 150) +
    0 * 1 * 1 (181 > 150) +
19720 * 1 * 1 (200 > 150) +
 4400 * 1 * 1 (220 > 150) +
30000 * 0 * 1 (230 > 150) +
20000 * 0 * 1 (240 > 150) = 24160

M7: (CurrentRowOrder = 200):

 4400 * 1 * 1 (220 > 200) +
30000 * 0 * 1 (230 > 200) +
20000 * 0 * 1 (240 > 200) = 4400

The general idea of calculation "Down Factor" using such approach, is to include rows, which Order is greater than Order of calculated row.

How can I perform such calculation in DAX? Can you help with some queries?

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-25T21:23:07

You can do it as a calculated column as follows:

DownOrder =
SUMX (
    Table1,
    Table1[Production] * Table1[Production Factor]
        * IF ( Table1[Order] > EARLIER ( Table1[Order] ), 1, 0 )
)

The SUMX iterates through each row in the table given in the first argument and sums the expression in the second. The EARLIER function allows you to grab the value from the (earlier) row context so you can compare each row to the current row.

You could write it like this too and it should work as measure:

DownOrder =
VAR CurrentOrder = SELECTEDVALUE ( Table1[Order] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Table1 ), Table1[Order] > CurrentOrder ),
        Table1[Production] * Table1[Production Factor]
    )

Calculate "Down factor" for each row using DAX in Power BI

1 Answers