Forum Discussion

sulleyinoz's avatar
sulleyinoz
Advocate II
2 years ago
Solved

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

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 Financi...
Need Help
  • sulleyinoz's avatar
    sulleyinoz
    2 years ago

    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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
sulleyinoz
Advocate II
2 years ago

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