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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

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] )
)