Skip to main content
cancel
Showing results for 
Search instead 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

Reply
valeriydik
Regular Visitor

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?

 

image.pngimage.png

 

1 ACCEPTED SOLUTION
v-sihou-msft
Microsoft Employee
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] ) )
)

1234.PNG

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Microsoft Employee
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] ) )
)

1234.PNG

 

Regards,

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.