Forum Discussion

ja342's avatar
ja342
Frequent Visitor
6 years ago

Divide sums across two tables

Hello, I am trying to create a new measure (or table) to output of the summation of 'Table1 BCM' divided by summation of 'Table2 SMU Hrs' by 'Dig Unit #' type as a filter.

 

I tried using this but got an error message saying cannot convert value of type text to type true/false.

 

BCM / hr Rate =
VAR BCM = CALCULATE(
SUM('Table1'[BCM]),
FILTER (Table1, Table1[Dig Unit #] ))

VAR SMU = CALCULATE(
SUM('Table2'[SMU Hrs]),
FILTER ('Table2', 'Table2'[Dig Unit #]))
RETURN
    DIVIDE(BCM,SMU)
Need Help

3 Replies

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

    Hi ja342 

    you need to compare Table1[Dig Unit #] and 'Table2'[Dig Unit #] with something inside FILTER()

    Now, it has completely incorrect syntax

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

    ja342 , what are you trying to filter

    FILTER (Table1, Table1[Dig Unit #] )

    give some value

    FILTER (Table1, Table1[Dig Unit #]  = "ABC")

    or

    FILTER (Table1, Table1[Dig Unit #] = related(Table2[Dig Unit #] ))

    • ja342's avatar
      ja342
      Frequent Visitor
      6 years ago

      I am trying to create a visual table that looks like this:

      Dig Unit #BCM / hr DayBCM / hr Night
      Ex11,250..
      Ex2

      1,230

      		..
      Ex2....

       

      The values inside this table is what I am trying to sum from two different tables.  The BCM value is in table 1 and Hr values are in table 2.  Both of which need to be summed up for this summary table above.  I tried merging queries and using relationships neither of which seemed to produce result needed.