Forum Discussion

V1R0S's avatar
V1R0S
New Member
2 years ago
Solved

Need help with dynamic weighted average

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 ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

V1R0S's avatar
V1R0S
New Member
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.