Measure help with multiplying a Table Field by a Measure ... leveraging a dataset relationship

[sulleyinoz]

Hi All, 

I'm having a brain spat and can't work this one out.  Its actually really simple in concept ... 

I have 2 tables, 1 with Pension Rates Per Year (shown below) and another with Financial Years (obviously just a list).  I need these to be in separate tables because it is part of a very complex 100 table PPM tool.

[PENSION RATE]

FY:            Rate:

2023          10.5%

2024          11%

2025          11.5%

The goal is to calculate the Pension amount based on the timesheets - which are broken down into days per project over multiple years.  Currently the rate is hardcoded into a measure "Pension Rate %".

CURRENT MEASURES:

Total Resource Pension $ = sumx('Timesheets', 'Timesheets'[Units] * [Daily Pension $])

 ... where, 

Daily Pension $ = [Daily Salary Base $] * [Pension Rate %]

So, I need this formula "Daily Pension $" to change to basically work out the Daily Pension $ using the [PENSION RATE] table above and it should leverage the relationship connection to the FY table to aumatically resolve which rate is used depending on the year it is in.

[Anonymous]

Hi,@sulleyinoz  I am glad to help you.
Hello, @RossEdwards ,thanks for your concern about this issue.

Your answer is excellent!
It's great to see that you were able to ask a question and get proper advice on the forum, and I noticed that you also shared the solution you used. This is great as it facilitates other users on the forum with similar problems to find a solution quickly. You can mark the suggestions you find helpful as solutions, which will help more forum users.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[RossEdwards]

The solution for Daily Pension $ needs to be similar to your solution for Total Resource Pension $. You need to use a SUMX over the VALUES used on your financial years.  Something like:

Daily Pension $ = SUMX(
    VALUES('Fiscal Years'[Year]),
    [Daily Salary Base $] * [Pension Rate %]
)

[sulleyinoz · Accepted solution]

Hi Ross ... yes ... total brainspat like I said ... I did work it out in the end when I sat back and had a coffee ... ended up like this in the real measure.

Daily Super FY $ = SumX('Resource Rates','Resource Rates'[Super] * [Daily Salary Base $])

... where Super is pension above and Resources Rates is the table holding the Pension Rates .... I have many other rates per FinYear in there too.

I will ask the purpose of VALUES ... is that just to make sure they are numbers being passed through instead of Text? I'm sure that's the case, but I ensure that in my Power Query processing anyway.

The Fiscal year works itself out in the data model via the relationship ... so all works out beautifully.

Thanks for taking the time to help.

Cheers

Andre