Forum Discussion

Pillsbury33's avatar
Pillsbury33
Icon for Helper I rankHelper I
6 years ago

How to slice across two tables

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.

 

Need Help

10 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Pillsbury33
      Icon for Helper I rankHelper I
      6 years ago

      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.

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Pillsbury33
      Icon for Helper I rankHelper I
      6 years ago

      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