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                        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!

 

 

  • 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.

3 Replies

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