Forum Discussion

Gazsim44's avatar
Gazsim44
Icon for Helper III rankHelper III
6 years ago

DIVIDE measures across two tables

Hi All, 

 

I have a situation where I need to obtain a productivity number by dividing two measures from two tables - one for FTE and one for number of shipments.

 

CYA Shipments = CALCULATE(SUM('PL Data'[Value]), FILTER('PL Data','PL Data'[Category]="Shipments"),'PL Data','PL Data'[Source]="Current Year Actual")

MSO FTE = CALCULATE(SUM('Prod Data'[Value]), FILTER('Prod Data','Prod Data'[Category]="MSO FTE"))

 

 
The commonality between these two tables is Branch / Month & Product and the resulting matrix table would need to resemble something as such with the Product being used as a slicer to switch between the two options.
BranchJanFebMar
A100105103
B506070
C439178
D150140145
E605550
 
Both tables contain the Branch / Month / Product more than once as there is the addition of different categories in each table although the number of shipments category is not common in both and is only in the PL Data. Same applies to the MSO FTE Category which is only contained within the Prod Data table.
 
Many thanks for any assistance you can provide, 
Need Help

3 Replies

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

    Hi, can you should us your relationship view in order to help us understanding the model. With this I don't know if DIVIDE (Measure1, Measure2, 0 ) would work for you or not.

     

    Regards,

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

    Hi Gazsim44 ,

     

    Assuming that you only have 2 tables from your description you need to create 3 tables to link the other two ( Branch / Month & Product) then use the columns from this new tables to make the matrix and the division will work.

     

    If this doesn't work or you need some assistance please share some data from both tables.

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    What's your relationships and link column between your two tables?

    Could you please share some sample data if convenient, and remember to remove any sensitive data.

    Thanks.

     

    Best Regards,

    Giotto Zhi