Need help with dynamic weighted average

[V1R0S]

Hi all, 

Im fairly new to PowerBI and been trying to create a Dax measure to calculate weighted average DPO.

Exemplary table:

Payment Days     Value     

60                        1000

60                        2000

50                        1500

30                        3500

30                         500

There are also different categories of materials and different regions, hence I want to to be reacting dynamically based on applied filters. 
First, it should build the sum of value per Payment Day
i.e. 60 days with overall 3000, 30 with 4000 etc.

OR divides the value by the sum of values to obtain the weight factor. Ultimately multiplying weight factor with Payment Day and then summing up all weighted average days to receive the overall weighted average days.


Based on a video ive watched on YT ive tried to apply formulas already. It reacts dynamically, just dont know if the values are correct. Can you maybe quickly check the code?

AverageDays =

var DTP =

SUMMARIZE(

    'Report',

    'Report'[PaymentDays],

    "ValuePerPT",

    SUMX('Report', 'Report'[Value(GC)])

)

    var DTP2 =

    ADDCOLUMNS(DTP,

    "Weight", DIVIDE(

[ValuePerPT],

SUMX(DTP, [ValuePerPT])

    )

    )

    var DTP3 =

    ADDCOLUMNS(DTP2, "WTDTP", [Weight] * 'Report'[PaymentDays])

    Return SUMX(DTP3, [WTDTP])

Thank you very much!

[V1R0S]

Thank you for this. How can I use a filter here? In my original file I have rows, that have a value but a blank "payment day" cell.

So, back to my example:

Payment Days     Value     

60                        1000

60                        2000

50                        1500

30                        3500

                            5000

30                         500

I want it to ignore the blank line item and the 5000 value. (Only blanks should not be counted, "0" values must not be excluded), so basically start with a filter operation (like Filter(List,NOT ISBLANK Payment Days)) before it processes the average days function

[Anonymous · Accepted solution]

Hi @V1R0S 

Please try this:

MEASURE =
VAR _DTP =
    FILTER (
        SUMMARIZE (
            'Report',
            'Report'[PaymentDays],
            "ValuePerPT", SUM ( Report[Value] )
        ),
        'Report'[PaymentDays] <> BLANK ()
    )
VAR _DTP2 =
    ADDCOLUMNS (
        _DTP,
        "Weight", DIVIDE ( [ValuePerPT], SUMX ( _DTP, [ValuePerPT] ) )
    )
VAR _DTP3 =
    ADDCOLUMNS ( _DTP2, "WTDTP", [Weight] * 'Report'[PaymentDays] )
RETURN
    SUMX ( _DTP3, [WTDTP] )

You can use the filter() function to filter the DTP to exclude the blank:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

average days: =
DIVIDE (
    SUMX ( Report, Report[Payment Days] * Report[Value] ),
    SUMX ( Report, Report[Value] )
)