removing members with 0 asset balance based on month selected, and calculating a simple average

[Benjwill]

Hi everyone, I have a very difficult problem to solve. I hope you could help me. I don't know if I've done a good job explaining stuff, but here it is, so sorry if I am not being clear...

I have 4 tables:

1. pensioner table, which is a list of pensioners' name, age, sex etc. like a DIM_Customer table.

2. pensioner withdrawal table: amount of money that each pensioner withdraws each month (monthly data)

3. pensioner balance table: amount of money left at the end of each month for each pensioner, after withdrawals are made (monthly data)

4. calendar table

Both pension withdrawal table and pensioner balance table link to pensioner table via pensioner ID , and link to calendar table via end-of-month date.

My task is to calculate the 3/6/9/12/YTD-month withdrawal rate: how much pension is withdrawn for N months, as a percentage of opening pensioner balance, depending on the month selected.

The below are my measures. If 30 September 2024 is selected as reporting month from Calendar, 6-month withdrawal rate calculation is as follows: 

1. how much pension is withdrawn during the previous 6 months: 

[Withdrawal] = CALCULATE(SUM(PENSIONER_WITHDRAWAL[WITHDRAWAL_AMT]),DATESINPERIOD(DIM_Calendar[Date],MAX(DIM_Calendar[Date]),-6,MONTH)

2. Pensioner opening balance at 31 Mar 2024 (which is 6 months before 30 Sept 2024):

[Opening balance] = CALCULATE(SUM(PENSIONER_BALANCE[CLOSING_BALANCE]),DATESMTD(DATEADD(DIM_Calendar[Date],-6,MONTH)))

3. Pension withdrawal rate = [Withdrawal] / [Opening balance]

However, there are two issues with this method.

A. I need to remove the pensioners who have zero $ opening balance (these pensioners only deposit and withdraw later than the opening balance month), which I don't know what to do.

B. currently the members with big money balance or big withdrawal skew the withdrawal rate. To fix this, I'd like to calculate the withdrawal for each member first, and then averaging all members' withdrawal rates, rather than sum all withdrawals and then divide by all opening balances like the current method.

Thank you very much for your help and support ... sorry if I am not being clear ...

[Anonymous · Accepted solution]

Hi @Benjwill ,

Here are some of the changes I made.

1. changed the “/” in Pension Withdrawal % - Selected to a DIVIDE function that performs division and returns the alternative result or BLANK() divided by 0.

2. change 'Pensioner Withdrawal'[Withdrawal Type] = “Pension” in the Pension withdrawal calculation condition to
FILTER('Pensioner Withdrawal', 'Pensioner Withdrawal'[Withdrawal Type] = "Pension" && 'AA_Measures'[Pension Opening Balance - Selected] <> 0)

3. found some measure where you may have accidentally written 3 instead of 2 and I changed it.

As a result, the final result has also changed, you can refer to the attachment to check if this is the result you expect.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Benjwill]

Hi Clara, actually could you help review these a bit more if it's OK?

Still with Reporting period selected as 30 Sept 2024, when I chose in the slicer "One month" or "Last 2 months", the column chart no longer display any columns.

Secondly, keeping the same 3 months, I looked at Pension withdrawals for Account51_1, both the pbix displays $33,127:

But should it be 42,252 (33,127 for Sept + 8039 for Aug + 1086 for Jul)? Even my original model (after fixing the error) was only able to sum Sept and Aug for 3 months period, so I am not sure I missed anything here...

 

[Benjwill]

Hi Clara, words can't describe how happy I am feel right now. Yes I made a typo in one of these measures and it should be changed.  

You're such an amazing Power BI SME and thanks again for helping me with power BI.

[Anonymous]

Hi @Benjwill ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Benjwill]

Hi Clara,

I've created a sample pbix file here with mock data (no confidential information). I was a little slow, it took me almost a day to create this file.

https://drive.google.com/file/d/1sO-foF0uEnFbfOeoIEke_4yT1-zZkQxq/view?usp=drive_link

I've explained a bit more in the pbix file.

As explained I wish to remove the zero opening balance for whenever period chosen.

For example, on the chart that for retail members aged 80 - 84, for last 3 months, pension withdrawal rate is 55.1%. 

But this rate is inflated, as can be seen in the table 1 below this chart in the pbix. Three members with 0 opening balance which I need to be removed.

Somehow including the opening balance filter for the table works. As in table 2:

So it should be 25.6% not 55.1%. As can be seen I am able to remove for tables, but not column chart.

Secondly, I wonder if there is a way to calculate the withdrawal for each member first, and then averaging all members' withdrawal rates, rather than sum all withdrawals and then divide by all opening balances like the current method.

Thank you very much in advance!

@bhanu_gautam @Poojara_D12  Google drive link is my pbix file if you guys could help me again it would be awesome!!!

[Benjwill]

woa woa thank you very much!!! I will prepare a simple pbix file and send you very soon 👏🙏

[Benjwill]

Hi bhatu_gautam thank you very much for your detailed reply.

I followed your steps but it also didn't seem to work... Probably because of some errors in my model.

[Poojara_D12]

It seems like you’re trying to calculate the withdrawal rate for pensioners over a period of months while addressing two issues:

1. Excluding pensioners who have a zero opening balance.
2. Averaging withdrawal rates across pensioners rather than summing withdrawals and balances, to avoid large balances or withdrawals skewing the result.

Let’s break down how to address both issues in your measures.

A. Excluding Pensioners with Zero Opening Balance

You want to exclude pensioners with a zero balance from your calculation. This can be done by adding a filter to your Opening Balance calculation, ensuring that you only include pensioners who have a non-zero opening balance.

For your Opening Balance measure, you can modify it like this:

DAX:
[Opening balance] = 
CALCULATE(
    SUM(PENSIONER_BALANCE[CLOSING_BALANCE]),
    DATESMTD(DATEADD(DIM_Calendar[Date], -6, MONTH)),
    PENSIONER_BALANCE[CLOSING_BALANCE] <> 0
)

This ensures that you only consider pensioners with a non-zero balance when calculating the opening balance for the 6-month period.

B. Averaging Withdrawal Rates per Pensioner

Instead of calculating the total withdrawal amount for all pensioners and then dividing by the total opening balance, you want to first calculate the withdrawal rate for each pensioner and then average the withdrawal rates. This will avoid the skewing effect caused by very high balances or withdrawals.

To achieve this, you can create two separate measures:

1. Calculate the withdrawal rate for each pensioner.
2. Average those rates across all pensioners.

Step 1: Withdrawal Rate for Each Pensioner

You can create a measure that calculates the withdrawal rate for each individual pensioner:

DAX:
[Individual Withdrawal Rate] = 
DIVIDE(
    SUM(PENSIONER_WITHDRAWAL[WITHDRAWAL_AMT]), 
    CALCULATE(
        SUM(PENSIONER_BALANCE[CLOSING_BALANCE]),
        DATESMTD(DATEADD(DIM_Calendar[Date], -6, MONTH)),
        PENSIONER_BALANCE[CLOSING_BALANCE] <> 0
    )
)

This calculates the withdrawal rate for each pensioner by dividing their total withdrawals over the last 6 months by their opening balance for that period. The filter ensures only non-zero balances are considered.

Step 2: Averaging the Withdrawal Rates

Now, you can average the individual withdrawal rates across all pensioners:

DAX
[Average Withdrawal Rate] = 
AVERAGEX(
    FILTER(
        ALL(PENSIONER), 
        PENSIONER_BALANCE[CLOSING_BALANCE] <> 0
    ),
    [Individual Withdrawal Rate]
)

This AVERAGEX function will calculate the average withdrawal rate for all pensioners, ensuring you exclude those with zero balances.

Final Withdrawal Rate Measure

Putting everything together, the final withdrawal rate measure can be written as:

DAX
[Withdrawal Rate] = 
DIVIDE(
    [Withdrawal], 
    [Opening Balance]
)

Where:

• [Withdrawal] is the total withdrawal for the last N months (e.g., 6 months, which you’ve already calculated).
• [Opening Balance] is the total opening balance for the same period, excluding pensioners with zero balances.

This approach will:

1. Correctly handle pensioners with zero opening balances.
2. Average the withdrawal rates per pensioner, avoiding skewing by large balances or withdrawals.

Conclusion

These modifications should help you solve the two key issues you're facing:

1. Excluding pensioners with zero opening balances from the calculations.
2. Averaging withdrawal rates across pensioners to avoid large balances or withdrawals skewing the result.

Let me know if you need further clarification or help with any of the steps!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[Benjwill]

Hi Poojara_D12, thank you very much for your detailed reply.

I followed your method up to step 2 where this measure gives me an error:

DAX
[Average Withdrawal Rate] = 
AVERAGEX(
    FILTER(
        ALL(PENSIONER), 
        PENSIONER_BALANCE[CLOSING_BALANCE] <> 0
    ),
    [Individual Withdrawal Rate]
)

It doesn't accept Pensioner and Pensioner_balance to be in the same filter 😞

[bhanu_gautam]

@Benjwill , Try using updated measures

Calculate the Withdrawal Amount:

[Withdrawal] =
CALCULATE(
SUM(PENSIONER_WITHDRAWAL[WITHDRAWAL_AMT]),
DATESINPERIOD(DIM_Calendar[Date], MAX(DIM_Calendar[Date]), -6, MONTH)
)

Calculate the Opening Balance:

[Opening Balance] =
CALCULATE(
SUM(PENSIONER_BALANCE[CLOSING_BALANCE]),
DATESMTD(DATEADD(DIM_Calendar[Date], -6, MONTH))
)

Filter Out Pensioners with Zero Opening Balance:

[Filtered Opening Balance] =
CALCULATE(
SUM(PENSIONER_BALANCE[CLOSING_BALANCE]),
DATESMTD(DATEADD(DIM_Calendar[Date], -6, MONTH)),
PENSIONER_BALANCE[CLOSING_BALANCE] > 0
)

Calculate the Withdrawal Rate for Each Pensioner

[Individual Withdrawal Rate] =
DIVIDE(
CALCULATE(
SUM(PENSIONER_WITHDRAWAL[WITHDRAWAL_AMT]),
DATESINPERIOD(DIM_Calendar[Date], MAX(DIM_Calendar[Date]), -6, MONTH)
),
CALCULATE(
SUM(PENSIONER_BALANCE[CLOSING_BALANCE]),
DATESMTD(DATEADD(DIM_Calendar[Date], -6, MONTH)),
PENSIONER_BALANCE[CLOSING_BALANCE] > 0
)
)

Calculate the Average Withdrawal Rate

[Average Withdrawal Rate] =
AVERAGEX(
VALUES(PENSIONER[PENSIONER_ID]),
[Individual Withdrawal Rate]
)