Forum Discussion

valeriydik's avatar
valeriydik
Regular Visitor
8 years ago
Solved

Weighted average based only on 3 latest date points

Hi all,
could you please help me with the case below?

 

I want "WEIGHTED PD0" to be an average value of "%PD 0 dpd" weighted by "*Balance bucket 0" (screen 1). The thing is that the weighted average must be calculated for any dimension ("cycle_name" is just example) and it also must work as on Excel screen 2, i.e. be summed up for only 3 latest points of report_dt (report_dt is always an end-of-month date).

 

Have you got any ideas?

 

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    valeriydik

     

    In your scenario, since you need a cumulative calculation for your "weighted avg", you must have an index column in your source table. Then you can write a measure like below:

     

    Measure =
CALCULATE (
    SUMX ( Table1, [%pd 0 dpd*] * [Balance Bucket*] )
        / SUM ( Table1[Balance Bucket] ),
    FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)

     

    Regards,

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    valeriydik

     

    In your scenario, since you need a cumulative calculation for your "weighted avg", you must have an index column in your source table. Then you can write a measure like below:

     

    Measure =
CALCULATE (
    SUMX ( Table1, [%pd 0 dpd*] * [Balance Bucket*] )
        / SUM ( Table1[Balance Bucket] ),
    FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)

     

    Regards,