How to slice across two tables

[Pillsbury33]

Hello everyone,

I’ve looked at a number of threads but can’t anything to help with this. I am trying to divide two columns from different tables and create a new measure. I have tried 100 different formulas but can’t get it right. Here’s what I have:

FTE PER SITE = TABLE 1[ZPs YTD]/Table 2[FTE]

I have tried plenty more complex formulas but at least this one gives me the right answer when no slice is selected.

In essence:

I have [ZPs YTD]) as the numerator in Table 1

The denominator is in Table 2 and is FTE

The PBI page I created is using a slicer from Table 1 for UserSite

I have linked the two tables as can be seen in the images below

 Relationship tableRelationship details

If I take Site A from Table 1 with 379 ZPs YTD and divide by Site A (these have the same names in each table) FTE in Table 2 of 465, the answer I should get is 0.81. I can get it to work with the total of FTE for all sites but when I slice by Site A the result I get is 0.029 i.e.379 divided by the total FTE of all sites.

I’ve been playing with this for 3 days and have tried literally hundreds of different. Thanks in advance for your help.

Cheers, Pillsbury 33.

[jdbuchanan71]

Hello @Pillsbury33 

In your model a Table 2 can filter Table 1 (it flows from the 1 to the many or downhill) but the other way doesn't work.  If you right click on your join you can open the properties you can set the filter direction to BOTH then table 1 can filter table 2.

[Pillsbury33]

Hello @jdbuchanan71 

Thanks for the speedy response. I had the relationship as 'Both" earlier but it made the Tile disappear i.e. turn gray with a cross. I have just done as suggested and this happened again. Here's the latest formula I have used:

 Ignore the errors, it does work (I have changed the names to match the earlier comments). These were from the same table. When I change ZP[FTE PER SITE] to Table 2[FTE PER SITE] the tile works again, but with the same problem that it only correctly shows the right calculation if no slice is selected.

Thanks, Pillsbury33

[jdbuchanan71]

@Pillsbury33 

Are [ZPs YTD] and [FTE PER SITE] measures or columns?

[Pillsbury33]

Measures. I tried Columns but got the same result. 

[jdbuchanan71]

When you reference a measure you don't need a table name in front of it so it should be.

ZPs/FTE = DIVIDE ( [ZPs YTD], [FTE PER SITE], 0 )

Would it be possible for you to share your .pbix?  The di-directional should have worked as far as I can tell. 

[Pillsbury33]

Hello @jdbuchanan71

My bad! I just happened to use the one example where the sites in the two tables where spelt slightly different. I changed the formula to this, but also added a new relationship between the UserSite and the FTE column (not sure if this was needed), corrected the name and 'hey presto!'.

Many thanks for your help!

[Pillsbury33]

Hello @jdbuchanan71 

Stupid question, I know, but how do I attach the PBIX? I can see every other type of attachment option, but not for a file.

Many thanks, Pillsbury33.

[jdbuchanan71]

You can load it to OneDrive or DropBox and then share the link here.

[Mariusz]

Hi @Pillsbury33 

Use the Site Name not UserSite

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Pillsbury33]

Hello @Mariusz

Thanks for the response. As below, I have done this but it still doesn't help with the slicer i.e. the visual ignores the slicer selection for the denominator and divides by the whole number but the slicer still recognises the numerator i.e. 300 site A ZPs / 12,000 total FTE rather than 300 site A ZPs / 456 site A FTEs.

Thanks, Pillsbury33.