Forum Discussion

8 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi Anonymous 

     

    Can you try this:

     

    Column = 

    VAR _1 = SUM ( Table1[Amount] )
    VAR _2 = SUM ( Table2[Amount] )
    VAR _3 = _1 / _2

    RETURN 

    IF ( _3 = 0 , 0 , _3 )

     

    Hope this helps.

    Theo

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    TheoC Thanks for answering. It works but gives wrong values.

    Other info- I have two tables related to each other on ID.

    Table 1 has columns-Start time, End Time, ID

    Table 2 has columns- ID, Volume

     

    Speed = divide volume by Time difference btw start and end time. And then need to further divide by max speed.

     

    Only measure is giving the correct value and not the calculated column.

    I have put the slicer of Start Time on the report based on which it will calculate max speed.

    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      Anonymous What measure are you using? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        TheoC  

        Measure 2 = sum('table2'[volume])/Sum('table1'[Time])
         
        Calculated column --> Time = (DATEDIFF('table1'[start time], 'table1'[end_time],SECOND))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    TheoC Thanks for your patience. I am sharing the details.

    Speed1 = SUM(Table2[Volume])/sum(Table1[Time])
    Time = (DATEDIFF('Table1'[start time], 'Table1'[end time],SECOND))

    These are two tables linked with each other on ID.If I divide using measure, it gives correct value but if I create a calculated column, it gives wrong value.

    I don't know how to share the PBIX file with you.

     

    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      Anonymous if there's a relationship using the IDs in both tables, then create following calculated columns:

       

      colRelated = RELATED ( [Volume] ) // This brings the Volume column into the other table

      colDivision = DIVIDE ( [Volume] , [Time] , 0 ) // This divides Volume by Time.

       

      Hope this helps.

      Theo

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    From this:

    Only measure is giving the correct value and not the calculated column.

    I have put the slicer of Start Time on the report based on which it will calculate max speed.

     

    It seems that the max speed is dynamiclly based on the selected date period in slicer, right?

    If so, calculated column is not available since it is static and could not be changed. You have to use measure instead.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.