Retirement Risk

[andrewmarston]

Hi

I am doing some work on retirement risk and am trying to visualise something in PowerBI that I have previously done in Excel.

Basically all staff over 60 are grouped based on number of years to retirement at 65 (Now, 1 Year, 2 Years etc), the chart then visualises this as a bar chart with a cumulative total FTE for each bar.

In Excel I was able to do simple SUMIFS statement for each of the Groups to give the cumulative value and then plotted it onto the bar chart. 

I have not yet been able to do this in PowerBI.

I have tried to create cumulative columns for each group based on age but this doesn't work as I end up with 6 groupings of the same bar chart (albeit it is the right data in each of the charts) forumla below.

5 Years = CALCULATE(SUM('Retirement Risk'[FTE]),FILTER('Retirement Risk','Retirement Risk'[Retirement Risk] = "Now" || 'Retirement Risk'[Retirement Risk] = "1 Year"|| 'Retirement Risk'[Retirement Risk] = "2 Years"|| 'Retirement Risk'[Retirement Risk] = "3 Years"|| 'Retirement Risk'[Retirement Risk] = "4 Years"|| 'Retirement Risk'[Retirement Risk] = "5 Years"))

Any help would be appreciated.

[v-piga-msft]

Hi @andrewmarston,

---

Basically all staff over 60 are grouped based on number of years to retirement at 65 (Now, 1 Year, 2 Years etc), the chart then visualises this as a bar chart with a cumulative total FTE for each bar.

In Excel I was able to do simple SUMIFS statement for each of the Groups to give the cumulative value and then plotted it onto the bar chart. 

I have not yet been able to do this in PowerBI.

---

If you want to get the cumulative value, you could have a reference of the formula below.

Measure =
CALCULATE (
    SUM ( 'Retirement Risk'[FTE] ),
    FILTER (
        ALL ( 'Retirement Risk' ),
        'Retirement Risk'[Retirement Risk] = MAX ( 'Retirement Risk'[Retirement Risk] )
            && 'Retirement Risk'[Date ] <= MAX ( 'Retirement Risk'[Date ] )
    )
)

And the output is below.

If you need additional help please share some data sample and your expected output, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry