# Filter for rate?

#### pvr928

##### Well-known Member
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
Code:
``=sumx(CALCULATETABLE(FTEData,FTEData[Manager]),2000/12)``

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

Code:
``=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

Last edited:

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Matt Allington

##### MrExcel MVP
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

Last edited:

Replies
1
Views
972
Replies
0
Views
245
Replies
0
Views
3K
Replies
9
Views
375
Replies
1
Views
226

1,191,607
Messages
5,987,635
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back