cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Weighted average based only on 3 latest date points

Hi all,

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?

1 ACCEPTED SOLUTION
Microsoft Employee

@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,

Microsoft Employee

@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,