PowerBI - combining column function with measure

Question

I have the following dataframe in PowerBI table:

mass (kg)    number of boxes
10           2
blank        3 
20           blank
blank        blank

First row means that two boxes were delivered and were weight as 10 kilograms together. Second row means that 3 boxes were delivered, but that the weighing system was broken. Third row means that the delivery person forgot to record the number of boxes (but 20 kilograms in total were delivered). And fourth row means no delivery.

No I am interested in the total amount of kilograms for each row that got delivered, in which the cases in which the weighing system was broken, the average weight of one box is used to calculate total weight:

Total mass (kg)
10
15
20
0

I already created a measure calculating the average weight of 1 box as:

Average weight per box (kg) = AVERAGEX( 'Table 1', DIVIDE('Table 1'[mass (kg)],'Table 1'[number of boxes]))

However, I get stuck now in creating the Total mass (kg) column in DAX coding.. how to solve this issue?

Rory Rory · Accepted Answer · 2019-05-02T11:56:51

The following calculated column returns:

BLANK() for rows where mass and number of boxes are missing
Your avg measure multiplied by the number of boxes where mass is missing
The original value for 'mass (kg)' for all other rows.

I've added an ALL() to stop the measure evaluating at row level, which I suspect is what prevented your original attempt from working.

Total mass (kg) =
IF (
    Table1[mass (kg)] = BLANK()  && Table1[number of boxes] = BLANK(),
    BLANK (),
    IF (
        Table1[mass (kg)] = BLANK(),
        CALCULATE ( [Average weight per box (kg)], ALL ( Table1 ) ) * Table1[number of boxes],
        Table1[mass (kg)]
    )
)

PowerBI - combining column function with measure

1 Answers