Filter for rate?

[pvr928]

Hi

Relatively new to PP.

I have two tables: Table 1 shows hours per employee per role per month over about 10 years.

Table 2 shows a single rate applicable to each year, and each year is different.

I have constructed a measure showing the hours for a particular role for each year:

Monthly Hours for Manager

=sumx(CALCULATETABLE(FTEData,FTEData[Manager]),2000/12)

I want to show the applicable yearly rate for each role, and am using:

=sumx(Fringe,Fringe[FringeRate])

However, this is simply aggregating the rates for each year (ie (0.2 + 0.25 + 0.3) = 0.75), rather than showing only the rate applicable for each year (ie 0.2, or 0.25, or 0.3, depending on the year).

Can I filter for each year in this Measure?

Am I not creating the relationship correctly possibly?

Any help greatly appreciated.

Cheers

pvr928

 

[Matt Allington]

It will depend on your table structure. what date fields do you have in table 1 and 2? Have you joined the tables? If so, how?
Are you writing measures? Read my article here

http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

If table 2 is joined 1 to many to table 1, then you should be able to write something like this

=sumx(fringe,fringe[rate] * [monthly hours for manager])

this measure operates a bit like sumproduct.

I have training courses in Sydney if you are interested
Public Power BI Training - Excelerator BI

and a great book for beginners. Http://xbi.com.au/learndax